Post reply

Help With DateTime Values

  • December 7, 2004 at 12:24 pm

    #532891

    I think comparing the time as floats might be a performance optimization

    SELECT CAST(GetDate() AS FLOAT) - FLOOR(CAST(GetDate() AS FLOAT))

    returns

    0.50975181327521568

     

  • December 7, 2004 at 3:25 pm

    #532927

    Without going into any paritcular can of worms I have seen this done as well and probably is the best method I have seen used without braking the storage you currently show.

    First I am assuming you are storing the times in datetime fields or smalldatetime fields.

    Next I assume you are going to use a variable object.

    Next I assume your always using hh:59:59 for end time.

    Lastly I am assuming a shift cannot be more than 24 hours.

    Now this is a psuedo of what I would do.

    DECLARE @time datetime

    SET @time = '2:00:00'

    WHERE

    (CASE WHEN @time >= Start_Shift THEN @time ELSE dateadd(d,1,@time) END) BETWEEN Start_Shift AND (CASE WHEN End_Shift >= Start_Shift THEN End_Shift ELSE dateadd(d,1,End_Shift) END)

     

    The key is to shift the lower hour to the next day so start and end are not reversed in order of each other.

Viewing 2 posts - 16 through 16 (of 16 total)

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