Friday, June 4, 2010

Compare Date only in Datetime field in T-SQL

This is simple still I want to share as this is asked by so many people around. So, here is the scenario why it's needed. [Mainly in reporting.]

From the UI, users selects the date and by using that date we need to compare the datetime field in database and produce some result. But, in database it actually the datetime field, we can't apply equal operator on it as it fails to do it because of time part.

I know, now there is a need of time for thinking on how to do it.

Solution:
SELECT * FROM [Employee] WHERE Convert(date,DateSent) = Convert(date,@Date);

OR

SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);

So, in above query we have taken out the Date part from both DateSent field in database and @Date parameter from the UI  and comparing them. Which produces exact result as we are looking for.

Hope this helps and want to know are there any better solutions for it.

2 comments: