CODEWITHSUNDEEP
sqlsql-serverperformancet-sqldatetime
George Menoutis
George Menoutis

Reputation: 7260

SQL partial date comparison

Which method is most efficient when comparing PARTS of date/datetime values? Example for comparing month of datetimes:

where insdate =DATEADD(month, DATEDIFF(month, 0, @insdate), 0)

or

where year(insdate)=year(@insdate) and month(insdate)=month(@insdate)

I'm using sql server

Upvotes: 0

Views: 1112

Answers (2)

DarioN1
DarioN1

Reputation: 2552

In my opinion, the best way to implement Year or YearMonth check is to cast date in this format YYYYMMDD and then work with that.

This is an example:

Filter by YearMonthDay

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) = 20170607

Filter by YearMonth

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) / 100 = 201706

Filter by Year

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) / 10000 = 2017

For sure this perfomrs better than using Year() ,Month() , DateAdd(), DateDiff() functions.

Upvotes: 1

iamdave
iamdave

Reputation: 12243

I disagree with Damien_The_Unbeliever's assertion that you should just use whichever reads cleaner, as there are objective reasons why one approach will be better than the other. The most pertinent of these is what is known as SARGability.

In essence this refers to whether SQL Server can use your values in the efficient manners it is designed to do, such as utilising indexes.

The differences in your two examples are nicely outlines here.

In short, if you have functions or calculated values on both sides of your equality conditions, SQL Server is definitely going to have to check every single value returned, whereas if you apply the principles of SARGability from the off, even if you don't see any significant benefits immediately you are at least in a better position to realise those benefits later on if required.

Upvotes: 1

Related Questions