datediff include 1 hour before midnight

  • 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.

  • 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.

  • 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

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

    Thank You

  • 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'.

  • 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

  • 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.

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

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