datediff include 1 hour before midnight

Question

datediff include 1 hour before midnight

Nooch

SSC Veteran

Points: 282
More actions
April 8, 2014 at 9:47 am

#306075

Hi,
I need to find all records for all 3 shifts in 1 day and the datediff below does not include the 1 hour before midnight (missing 1 hour for graveyard shift).
where DateDiff(day, Start_DateTime, '2014-04-07')=0 AND DateDiff(day, End_DateTime, '2014-04-07')=0
Thank You for you time.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

awcrestview (4/8/2014)
Hi,
I need to find all records for all 3 shifts in 1 day and the datediff below does not include the 1 hour before midnight (missing 1 hour for graveyard shift).
where DateDiff(day, Start_DateTime, '2014-04-07')=0 AND DateDiff(day, End_DateTime, '2014-04-07')=0
Thank You for you time.

Based on the date 2014-04-07 what are the dates and times of the shifts you are trying to return.

Nooch SSC Veteran Points: 282 More actions · Answer 2

Thank You for your reply.

I need records for user selected date. In this example I'm using 4/7/14.

The shifts are;

grave = 11pm to 7am

day = 7am to 3pm

swing = 3pm to 11pm

thanks

Nooch SSC Veteran Points: 282 More actions · Answer 3

The start_datetime for this example would be graveyard shift which starts at 11pm on 4/6/14.

Thank You

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

awcrestview (4/8/2014)
The start_datetime for this example would be graveyard shift which starts at 11pm on 4/6/14.
Thank You

Okay, so what you are looking for is the following based on a the date 2014-04-07 is this:

where startdate >= '2014-04-06 23:00' and enddate < '2014-04-07 23:00'

Is this a good paraphrase of what you need? If so, then something like this:

WHERE startdate >= dateadd(hour, -1, dateadd(day, datediff(day, 0, getdate()), 0)) and enddate < dateadd(hour, -1, dateadd(day, datediff(day, 0, getdate()), 1))

This assumes the getdate() returns '2014-04-07 hh:mm:nnn.nn'.

Nooch SSC Veteran Points: 282 More actions · Answer 5

Lynn,

Thanks for you help Lynn, you're awesome. Below is from your recommendation with minor change to get what I needed.

WHERE start_datetime >= dateadd(hour, -1, dateadd(day, datediff(day, 0, (getdate)), 0)) and DateDiff(day, End_DateTime, 'getdate')=0

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

awcrestview (4/8/2014)
Lynn,
Thanks for you help Lynn, you're awesome. Below is from your recommendation with minor change to get what I needed.
WHERE start_datetime >= dateadd(hour, -1, dateadd(day, datediff(day, 0, (getdate)), 0)) and DateDiff(day, End_DateTime, 'getdate')=0

Just realize that this part of the where clause, DateDiff(day, End_DateTime, 'getdate') = 0, is not SARGable meaning you will not make use of any indexes. This part requires the every row needs to be tested to determine if the difference in days between End_DateTime and GETDATE() = 0.