Help With DateTime Values

• John F

  SSCommitted

  Points: 1597

  More actions

  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

   

• Antares686

  SSC Guru

  Points: 125444

  More actions

  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.