Converting a Time into 12 Hour Format in SQL

Converting a time into 12 hour format in SQL

Do you have the current time in a variable/column of type time? If so, it's easy:

declare @t time
set @t = '14:40'

select CONVERT(varchar(15),@t,100)

result:

2:40PM

If it's in a datetime(2) variable, then you'll have to strip out the date portion after running the CONVERT. If it's in a string, then first CONVERT it to time, then use the above code.

Converting datetime format to 12 hour

If you want to convert the current datetime for example:

SELECT CONVERT(VARCHAR, getdate(), 100) AS DateTime_In_12h_Format

Instead of getdate() you can put your desired column in a query (such as tdate in your example). If you want JUST the time in 12h and not the date and time use substring/right to separate them. It seems that you already know how to =).

This page lists every datetime conversion. It's really handy if you need other types of conversions.

SQL Automatically converts 24 hour format time to 12 hour format

hh gives you 12 hour hours
HH gives you 24 hour hours

Also, mi is probably a typo, as it will return you a minute, followed by the letter i. Try this:

declare @datetime datetime = '2017-08-29 16:30:01'
select Format(@datetime,'dd/MM/yyyy HH:mm:ss') as SlotStartTime

You can find all valid datetime formatting characters here: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Format time using 12 hour notation

There is a syntax error on case when, try the below.

select convert(varchar(10),[Date],23) as [Date]
    , case when datepart(hour, [Time])> 12
    then (datepart(hour, [Time])- 12) 
    else datepart(hour, [Time]) End as Hour
    , datepart(minute, [Time])as Minutes, 
FROM [SQLIOT].[dbo].[ZEPB_CaseLog] 

Convert VARCHAR 24 Hour Time to 12 Hour

It is easy with FORMAT function:

SELECT timestr
     , FORMAT(CONVERT(DATETIME, timestr, 108), 'h:mm t')
     , FORMAT(CONVERT(DATETIME, timestr, 108), 'h:mm tt')
FROM (VALUES
    ('17:45'),
    ('09:00')
) t(timestr)

Note that tt format specifier does not work with time datatype.

SQL (HUE) : Is there any way to convert 24 hrs time into 12 hrs AM / PM format with hours buckets

Please use below code. Replace now() with time for your query.

SELECT now(), lpad(CONCAT ( 
CAST (extract(hour from now()) + CASE WHEN extract(hour from now()) >12 THEN -12 
WHEN extract(hour from now())=0 THEN 12 
ELSE 0  END AS string) , 
CASE WHEN extract(hour from now())  >=12 THEN ' PM' ELSE ' AM' END),5,'0') as new_time 

Explanation -
firstly i am checking if hour is >12. If yes, deducting 12 to get the hour.

Then setting up AM/PM based on hour.

lpad is used to make sure you get data in 01 AM format.

Query to Convert a specific 12 hour format type to 24 hour format type in sql server 2016

Assuming that the format is M/d/yy h:mmA (or P) then I would use the style code 22, which is for mm/dd/yy hh:mi:ss AM (or PM), so that this works regardless of language setting:

SELECT CONVERT(datetime2(0),V.YourDate + 'M',22)
FROM (VALUES('3/2/20 3:30P'))V(YourDate);

---

Related Topics