1

I am using the statement below to separate the date and time from one column into two. I get a result like this. 

AppointmentDate AppointmentTime
10/11/2017      08:30:00.0000000
10/11/2017      16:50:00.0000000

How do I get the time to be in non military time and without the seconds and the milliseconds? Or if that's not possible, just remove the seconds and milliseconds?.

SELECT   
    CONVERT(varchar(19),ScheduleEntry.ScheduleDate,101) as AppointmentDate,
    CONVERT(time,ScheduleEntry.ScheduleDate) as AppointmentTime
TT.
  • 15,774
  • 6
  • 47
  • 88
John Molina
  • 179
  • 3
  • 14

1 Answers1

0

For 12-hr format, use:

SELECT format(getdate(), 'hh:mm tt');

And for 24-hr format, use:

SELECT format(getdate(), 'HH:mm');

Here are some other ways of formatting date-time:

SELECT format(getdate(), 'yyyy/MM/dd hh:mm tt'); -- 2017/10/10 06:30 PM

SELECT format(getdate(), 'yyyy/MM/dd HH:mm'); -- 2017/10/10 18:30

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

I hope this helped.

chaosifier
  • 2,666
  • 25
  • 39
  • Thanks. I knew this. I just don't know how to modify the statement to get what I want. FYI the where clause is WHERE datediff(d, getdate(), ScheduleDate) = 1. I'm getting appointment for tomorrow today. – John Molina Oct 10 '17 at 15:33
  • @JohnMolina I've updated the answer. Please have a look and accept this as an answer if it solves your problem. – chaosifier Oct 10 '17 at 16:30