Extract Time from DateTime field

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!

• Posted in:
• SQL

No comments