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?

Answer 1

On SQL Server 2008, you would have a new DATE data type, which you could use to achieve this:

SELECT (list of fields) FROM dbo.YourTable WHERE dateValue BETWEEN     CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) 

The CAST(GETDATE() AS DATE) casts the current date and time to a date-only value, e.g. return '2010-04-06' for April 6, 2010. Adding one day to that basically selects all datetime values of today.

In SQL Server 2005, there's no easy way to do this - the most elegant solution I found here is using numeric manipulation of the DATETIME to achieve the same result:

SELECT (list of fields) FROM dbo.YourTable WHERE dateValue BETWEEN     CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AND     DATEADD(DAY, 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) 

Answer 2

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 :)