Search code examples
sqlsql-serverdatetimessms-2012

Convert hour to 12 hr format after extracting 'hour' using DatePart(hour,Datetime)

I currently have a datetime field in my database. What I am doing already is extracting the HOUR, Minute and AM/PM part from the date since I have to populate each value into a separate field.

This is what the query looks like:

Select 'Hour'=(SELECT DATEPART(HOUR, Date1)),
'Minute'=(SELECT DATEPART(MINUTE, Date1)),
'PM'= (CASE WHEN DATEPART(HOUR, Date1) > 11 THEN 'PM' ELSE 'AM' END)
from tblA

So here I am able to have the three values. However, the issue I have is the HOUR part, by default is displayed in a 24 hour format. Is there any way to convert it to 12 hour format?

I am aware that I can use the format = 100 to convert time to 12HR format like so:

select convert(varchar(20),GetDate(),100)

However, when I apply this to just the 'HOUR' value, it doesn't change the format. Is there another way to accomplish this?

To clarify, if the datetime value in my table is: 2018-11-22 14:30:00.000

I am hoping to get a result that looks like this:

Hour      Minute       PM
2         30           PM
Solution

  • You can use the modulo operator.

    CASE
  WHEN DATEPART(HOUR, Date1) % 12 = 0 THEN
    12
  ELSE
    DATEPART(HOUR, Date1) % 12
END