CODEWITHSUNDEEP
sqlt-sqlstored-proceduresperformance
Dale Marshall
Dale Marshall

Reputation: 1135

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

Upvotes: 6

Views: 31859

Answers (8)

saurabh_hcl
saurabh_hcl

Reputation: 1

Compare two dates after converting into same format like below.

where CONVERT(varchar, createddate, 1) = CONVERT(varchar, getdate(), 1);

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146557

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

Upvotes: 9

Tomalak
Tomalak

Reputation: 338326

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.

Upvotes: 13

Marc Gravell
Marc Gravell

Reputation: 1063714

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)

Upvotes: 5

kristof
kristof

Reputation: 53844

I would normally use the solution suggested by Tomalak, but if you are really desperate for performance the best option could be to add an extra indexed field ReceivedDataPartOnly - which would store data without the time part and then use the query

declare @today as datetime
set @today = datediff(d, 0, getdate())

select     
    count(job) as jobs
from         
    dbo.job
where     
    received_DatePartOnly = @today

Upvotes: 0

James Curran
James Curran

Reputation: 103545

How 'bout

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

Upvotes: 0

ʞɔıu
ʞɔıu

Reputation: 48446

I'm not sure how you're defining "best" but that will work fine.

However, if this query is something you're going to run repeatedly you should get rid of the get_date() function and just stick a literal date value in there via whatever programming language you're running this in. Despite their output changing only once every 24 hours, get_date(), current_date(), etc. are non-deterministic functions, which means that your RDMS will probably invalidate the query as a candidate for storing in its query cache if it has one.

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

that's pretty much the best way to do it. you could put the DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) and DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1) into variables and use those instead but i don't think that this will improve performance.

Upvotes: 0

Related Questions