Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to check for current date in where clause of sql query

I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using:

SELECT     COUNT(Job) AS Jobs
FROM         dbo.Job
WHERE     (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
                        AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1))
like image 289
Dale Marshall Avatar asked Nov 28 '08 14:11

Dale Marshall


People also ask

Where condition check current date in SQL?

SQL Server provides several different functions that return the current date time including: GETDATE(), SYSDATETIME(), and CURRENT_TIMESTAMP. The GETDATE() and CURRENT_TIMESTAMP functions are interchangeable and return a datetime data type. The SYSDATETIME() function returns a datetime2 data type.

Which query is used to get the current date select one?

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE , and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

How do I get the current date in SQL without the time?

First Convert the date to float (which displays the numeric), then ROUND the numeric to 0 decimal points, then convert that to datetime.


Video Answer


3 Answers

WHERE
  DateDiff(d, Received, GETDATE()) = 0

Edit: As lined out in the comments to this answer, that's not an ideal solution. Check the other answers in this thread, too.

like image 111
Tomalak Avatar answered Oct 10 '22 02:10

Tomalak


If you just want to find all the records where the Received Date is today, and there are records with future Received dates, then what you're doing is (very very slightly) wrong... Because the Between operator allows values that are equal to the ending boundary, so you could get records with Received date = to midnight tomorrow...

If there is no need to use an index on Received, then all you need to do is check that the date diff with the current datetime is 0...

Where DateDiff(day, received, getdate()) = 0

This predicate is of course not SARGable so it cannot use an index... If this is an issue for this query then, assuming you cannot have Received dates in the future, I would use this instead...

Where Received >= DateAdd(day, DateDiff(Day, 0, getDate()), 0) 

If Received dates can be in the future, then you are probably as close to the most efficient as you can be... (Except change the Between to a >= AND < )

like image 29
Charles Bretana Avatar answered Oct 10 '22 01:10

Charles Bretana


If you want performance, you want a direct hit on the index, without any CPU etc per row; as such, I would calculate the range first, and then use a simple WHERE query. I don't know what db you are using, but in SQL Server, the following works:

// ... where @When is the date-and-time we have (perhaps from GETDATE())
DECLARE @DayStart datetime, @DayEnd datetime
SET @DayStart = CAST(FLOOR(CAST(@When as float)) as datetime) -- get day only
SET @DayEnd = DATEADD(d, 1, @DayStart)

SELECT     COUNT(Job) AS Jobs
FROM         dbo.Job
WHERE     (Received >= @DayStart AND Received < @DayEnd)
like image 31
Marc Gravell Avatar answered Oct 10 '22 00:10

Marc Gravell