Get row where datetime column = today - SQL server noob

Question

In sql 2005, instead of building a query from dateparts year, month and date,

is there an more succinct way of writing the where clause?

question from:https://stackoverflow.com/questions/2583228/get-row-where-datetime-column-today-sql-server-noob

Answer 1

In SQL 2000 and SQL 2005 you can use a nice select statement to remove the time component from a DateTime, ie

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

will return 6-Apr-2010 (well for today only).

So combined with marc_s's answer, you'd want

SELECT (list of fields)
FROM dbo.YourTable
WHERE dateValue BETWEEN DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 0) 
AND DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 1)

Edit: Changed to suit requirements, note the 1 in the second DateAdd (this adds the days it has been from the beginning to 1 (instead of 0), making it 7th Apr 2010 00:00:00) If you want 6th Apr 23:59:59 you take a second away from the second date

DATEADD(ss,-1,'My DateTime')

Final Call would become

DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd,0,'MY Date and Time, But I Only Want Date'), 1))

Ok that's alot of info at once! Hope it all makes sense :)