Format date/time to HH:mm-CodePudding

How do I get my output to be HH:mm (09:40) and not HH:mm:ss xxxxxxx (09:40:00.0000000)

Below is my query

SELECT 
  PatientMRN, 
  first_ScheduleTimeSlotDate, 
  CASE WHEN ExaminationTypecode in ('MSMBO') THEN dateadd(
    HOUR,-1, first_ScheduleTimeSlotTime
  ) WHEN ExaminationTypecode not in ('MSMBO') 
  and EX_Sub_Modality like 'MR%' THEN dateadd(
    MINUTE,-15, first_ScheduleTimeSlotTime
  ) WHEN ExaminationTypecode not in ('MSMBO') 
  and EX_Modality in ('Ultrasound', 'CT', 'MRI') 
    THEN (first_ScheduleTimeSlotTime) ELSE '' END as appointment_time1

I want the output to look like appointment_time but I get appointment_time1

appointment_time    appointment_time1
09:40               09:40:00 0000000
11:15               11:15:00 0000000
12:20               12:20:00 0000000
15:05               15:05:00 0000000
08:00               08:00:00 0000000

CodePudding user response：

You can achieve it using format function. Below is an example which extracts the hour and minute parts in the format you wanted from appointment_time1 field.

;with cte AS 
(
    SELECT 
      PatientMRN, 
      first_ScheduleTimeSlotDate, 
      CASE WHEN ExaminationTypecode in ('MSMBO') THEN dateadd(
        HOUR,-1, first_ScheduleTimeSlotTime
      ) WHEN ExaminationTypecode not in ('MSMBO') 
      and EX_Sub_Modality like 'MR%' THEN dateadd(
        MINUTE,-15, first_ScheduleTimeSlotTime
      ) WHEN ExaminationTypecode not in ('MSMBO') 
      and EX_Modality in ('Ultrasound', 'CT', 'MRI') 
        THEN (first_ScheduleTimeSlotTime) ELSE '' END as appointment_time1
)
select PatientMRN,
       first_ScheduleTimeSlotDate, 
       FORMAT(appointment_time1, N'hh:mm') AS appointment_time,
       appointment_time1,
from cte

CodePudding user response：

IF you change

THEN (first_ScheduleTimeSlotTime) ELSE '' END

THEN (first_ScheduleTimeSlotTime) ELSE NULL END

then the engine will not convert the date/time values to strings (it has to since you're specified a string value as the "default". That will leave the values as date/times (like the first column) and you can format them however you want when you display them.