CODEWITHSUNDEEP
sqlsql-servert-sqldate
Lookup
Lookup

Reputation: 17

How can i pull only Hour Number with AM/PM in SQL SERVER

My Input :

2020-01-01 09:01:00.000
2020-01-03 18:01:00.000

My Output Should be like :

9 AM
6 PM

Upvotes: 0

Views: 1317

Answers (5)

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

Your query.

SELECT convert(VARCHAR(30), cast('2020-01-01 09:01:00.000' AS DATETIME), 100)
    ,convert(VARCHAR(30), cast('2020-01-03 18:01:00.000' AS DATETIME), 100)

Upvotes: 0

Thom A
Thom A

Reputation: 95554

I would do: 

SELECT V.YourColumn,
       LTRIM(SUBSTRING(ca.v,13,2) + ' ' RIGHT(ca.v,2))
FROM (VALUES(CONVERT(Datetime2(3),'2020-01-01 09:01:00.000')),
            (CONVERT(Datetime2(3),'2020-01-03 18:01:00.000')))V(YourColumn)
     CROSS APPLY (VALUES(CONVERT(varchar(30),V.YourColumn,0))) ca(v);

In the CROSS APPLY I convert the datetime to the format MMM d YYYY h:mmAM/PM, then you can use SUBSTRING/LEFT to get the bits you want.

An alternative approach would be to use DATEPART:

SELECT CONCAT(CASE DATEPART(HOUR,V.YourColumn) WHEN 12 THEN 12 WHEN 0 THEN 12 ELSE DATEPART(HOUR,V.YourColumn) % 12 END,' ', RIGHT(CONVERT(varchar(30),V.YourColumn,0),2))
FROM (VALUES(CONVERT(Datetime2(3),'2020-01-01 09:01:00.000')),
            (CONVERT(Datetime2(3),'2020-01-03 00:01:00.000')),
            (CONVERT(Datetime2(3),'2020-01-03 18:01:00.000')))V(YourColumn);

Because 12 % 12 and 0 % 12 both = 0, then you need the CASE expression to handle those.

Upvotes: 0

BarneyL
BarneyL

Reputation: 1362

You can use the format function like this as long as you're using SQL Server 2012 or later.

SELECT FORMAT(GETDATE(),'h tt') AS MyTime

Note the use of the lower case 'h' which gives a 12 hour clock with no leading zero, 'HH' gives a two digit 24 hour clock so you end up with output like '16 PM'.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conversion : 

SELECT CONVERT(varchar(10), CONVERT(TIME, '2020-01-01 09:01:00.000'), 100)

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131219

If the input is a date-related type, you can use FORMAT with the appropriate format string, eg :

SELECT FORMAT(getdate(), 'HH:mm tt')

For me, this returns :

12:51 PM

Just HH tt returns the desired string, in this case :

12 PM

Upvotes: 0

Related Questions