CODEWITHSUNDEEP
sqlsql-serverdatetimequery-performance
Vergil
Vergil

Reputation: 79

SQL Server: compare only month and day - SARGable

I have a table storing a datetime column, which is indexed. I'm trying to find a way to compare ONLY the month and day (ignores the year totally).

Just for the record, I would like to say that I'm already using MONTH() and DAY(). But I'm encountering the issue that my current implementation uses Index Scan instead of Index Seek, due to the column being used directly in both functions to get the month and day.

There could be 2 types of references for comparison: a fixed given date and today (GETDATE()). The date will be converted based on time zone, and then have its month and day extracted, e.g.

DECLARE @monthValue DATETIME = MONTH(@ConvertDateTimeFromServer_TimeZone);
DECLARE @dayValue DATETIME = DAY(@ConvertDateTimeFromServer_TimeZone);

Another point is that the column stores datetime with different years, e.g.

1989-06-21 00:00:00.000
1965-10-04 00:00:00.000
1958-09-15 00:00:00.000
1965-10-08 00:00:00.000
1942-01-30 00:00:00.000

Now here comes the problem. How do I create a SARGable query to get the rows in the table that match the given month and day regardless of the year but also not involving the column in any functions? Existing examples on the web utilise years and/or date ranges, which for my case is not helping at all.

A sample query:

Select t0.pk_id 
From dob t0 WITH(NOLOCK) 
WHERE ((MONTH(t0.date_of_birth) = @monthValue AND DAY(t0.date_of_birth) = @dayValue))

I've also tried DATEDIFF() and DATEADD(), but they all end up with an Index Scan.

Upvotes: 4

Views: 1043

Answers (1)

Thom A
Thom A

Reputation: 95989

Adding to the comment I made, on a Calendar Table.

This will, probably, be the easiest way to get a SARGable query. As you've discovered, MONTH([YourColumn]) and DATEPART(MONTH,[YourColumn]) both cause your query to become non-SARGable.

Considering that all your columns, at least in your sample data, have a time of 00:00:00 this "works" to our advantage, as they are effectively just dates. This means we can easily JOIN onto a Calendar Table using something like:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate;

Now, if we're using the table from the above article, you will have created some extra columns (MonthNo and CDay, however, you can call them whatever you want really). You can then add those columns to your query:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate
WHERE CT.MonthNo = @MonthValue
  AND CT.CDay = @DayValue;

This, as you can see, is a more SARGable query.

If you want to deal with Leap Years, you could add a little more logic using a CASE expression:

SELECT dob.[YourColumn]
FROM dob
     JOIN CalendarTable CT ON dob.DateOfBirth = CT.CalendarDate
WHERE CT.MonthNo = @MonthValue
  AND CASE WHEN DATEPART(YEAR, GETDATE()) % 4 != 0 AND CT.CDat = 29 AND CT.MonthNo = 2 THEN 28 ELSE CT.Cdat END = @DayValue;

This treats someone's birthday on 29 February as 28 February on years that aren't leap years (when DATEPART(YEAR, GETDATE()) % 4 != 0).

It's also, probably, worth noting that it'll likely be worth while changing your DateOfBirth Column to a date. Date of Births aren't at a given time, only on a given date; this means that there's no implicit conversion from datetime to date on your Calendar Table.

Edit: Also, just noticed, why are you using NOLOCK? You do know what that does, right..? Unless you're happy with dirty reads and ghost data?

Upvotes: 3

Related Questions