Get row where datetime column = today
sql-server, sql-server-2005
Solution
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 :)
Problem
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?