sqlsql-servert-sqlquery-designer

what should be order of YEAR() MONTH() DAY() in a tsql where clause?

What should be the order of YEAR DAY MONTH in a where clause of tsql? 

Select * from TABLE_NAME 
WHERE
YEAR(COLUMN_NAME)=YEAR(GETDATE()) --1
and DAY(COLUMN_NAME)=DAY(GETDATE()) --2
and MONTH(COLUMN_NAME)=MONTH(GETDATE()) --3

Does the order improve the run time of query? how does a tsql execute this statement?

Solution

  • If you want to improve performance, then you are doing it wrong. The filters that you are using won't use an index (if there exists one on COLUMN_NAME) because you are applying a function to them. The right way to do it would be to compare that column directly against GETDATE() (on this case). This is a better way:

    SELECT * 
FROM TABLE_NAME 
WHERE COLUMN_NAME >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
AND COLUMN_NAME < DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),1)