-2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Som
  • 460
  • 1
  • 5
  • 11
  • 4
    Are you trying to do a date comparison whilst ignoring the time? ; http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part ; http://stackoverflow.com/questions/9308635/best-way-to-compare-dates-without-time-in-sql-server – Alex K. Jul 11 '13 at 13:26
  • 1
    Which version of sqlserver are you using ? – t-clausen.dk Jul 11 '13 at 13:36
  • How does sql parse a where clause? right to left – Som Jul 12 '13 at 20:07

1 Answers1

5

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)
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • 1
    @t-clausen.dk No, you are wrong. The `GETDATE()` will be evaluated only once and so will the `DATEADD()` function calls, in this query. – ypercubeᵀᴹ Jul 11 '13 at 14:00
  • 2
    @t-clausen.dk please find examples. In every situation I've seen, SQL Server is smart enough to only evaluate GETDATE() once, so I'm not sure how it could lead to a slow query. – Aaron Bertrand Jul 11 '13 at 14:02