Get row where datetime column = today - SQL server

问题:

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?

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

回答2:

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

回答3:

Not good performance, but a solution:

SELECT * 
FROM tblDate
WHERE CONVERT(VARCHAR, date, 112) = CONVERT(VARCHAR, GETDATE(), 112);

If it is a common query a computed column should be added with only year-month-day content.

回答4:

Something along the lines of this

CONVERT(date,tbldata.date)=CONVERT(date,getdate())

This assumes that the date column includes the time as well. If it does not then you can change it to

tbldata.date=CONVERT(date,getdate())

回答5:

I've used a UDF to do this on datetime columns going back to SQL 2000.

CREATE FUNCTION [dbo].[GETDATENOTIME](@now datetime)
RETURNS smalldatetime
AS
BEGIN
    RETURN (CONVERT(smalldatetime, CONVERT(varchar, @now, 112)))
END

Usage:

DECLARE @date smalldatetime
SET @date = '4/1/10'
SELECT * FROM mytable WHERE dbo.GETDATENOTIME(date) = @date

Not the most efficient thing in the world (YMMV depending on your table) but it does the job. For tables where I know I'll be selecting based on date-without-time a lot, I'll have a column specifically for that with a default set to dbo.GETDATENOTIME(GETDATE()).