Extract Time from DateTime field

June 16, 2008

I had a problem in MS SQL Server, where I need to do some calculations on the time part of a datetime field, to see how many people had arrived late for the job they were booked for. Normally I'd use the DATEDIFF function in SQL Server. For example:

SUM(
  CASE 
    WHEN 
      DATEDIFF(n, bookingstart, arrived) > 0 THEN 1 
    ELSE 
      0 
    END
  ) 
  AS WorkerLate

However I am storing the date and time in the "bookingstart" and just a time in the "arrived" field, so I can't use the DATEDIFF function directly. The solution is to extract the time and then use the DATEDIFF function:

SUM(
  CASE 
    WHEN 
      DATEDIFF(n, CONVERT(VARCHAR(10), bookingstart, 108), CONVERT(VARCHAR(10), arrived, 108)) > 0 THEN 1 
    ELSE 
      0 
    END
  ) 
  AS WorkerLate

Hope that helps someone!

 

No comments

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.