CODEWITHSUNDEEP
sqlsql-serverdatetimessms-2012
kooshy
kooshy

Reputation: 134

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

Upvotes: 0

Views: 693

Answers (2)

Anusha Subashini
Anusha Subashini

Reputation: 397

Try this,

DECLARE
   @Date1 dateTime;
SET @Date1 ='2018-11-22 14:30:00.000';

SELECT
   'Hour' = CASE WHEN (SELECT DATEPART(HOUR, @Date1)) > 12 THEN (SELECT DATEPART(HOUR, @Date1)) - 12 ELSE (SELECT DATEPART(HOUR, @Date1)) END,
   'Minute' = (SELECT DATEPART(MINUTE, @Date1)),
   'PM' = (CASE WHEN DATEPART(HOUR, @Date1) > 11 THEN 'PM' ELSE 'AM' END)

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can use the modulo operator.

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

Upvotes: 1

Related Questions