Format time using 12 hour notation

Question

I've made an SQL query which shows Date and Time, with Time split into Hours and Minutes.

select convert(varchar(10),[Date],23) as [Date], 
datepart(hour, [Time])as Hour, datepart(minute, [Time])as Minutes, 
FROM [SQLIOT].[dbo].[ZEPB_CaseLog]

The table shows Hours in 24-hour format. But I want to have it in 12-hour format instead, with single digits having a '0' prefix.

i.e: 01, 02, 03, etc.

I thought of using case to do it:

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

Doing so gives me a syntax error on line

datepart(hour, [Time])> 12 then (datepart(hour, [Time])- 12) as Hour

--Incorrect syntax near the keyword 'as'.

I'm not too familiar with doing subtractions in SQL itself. Is there anything else I should add to fix this?

When it comes to datetime, you need to use the relevant functions, which are all well documented - if you really did need to add/subtract hours, you would use `dateadd` but as its just formatting, use `format`. — Dale K, Sep 07 '20 at 01:15
I tried your suggestion but SQL gives me error saying `'format' is not a recognized built-in function name.` — hjh93, Sep 07 '20 at 01:43
My company uses it. I have no say in the matter lol. For now I'll give dateadd a try — hjh93, Sep 07 '20 at 01:48

score 3 · Accepted Answer · edited Sep 07 '20 at 02:31

3

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]

edited Sep 07 '20 at 02:31

Dale K

25,246
15
42
71

answered Sep 07 '20 at 01:49

SQLZealots

42
2

Works great. But if I want to show prefix on single digit numbers (01, 02, etc.), what should I add? I forgot to add that on my question. – hjh93 Sep 07 '20 at 02:13
I just found the answer to that. https://stackoverflow.com/questions/5540064/padleft-function-in-t-sql – hjh93 Sep 07 '20 at 02:37

Dale K · Answer 2 · 2020-09-11T04:33:19.403

Given you are interested in formatting, i.e. display, then lets use strings.

declare @Time time = '09:33:44 pm'

select @Time [Original]
  , format(cast(@Time as datetime),N'hh') [Format Hour] -- SQL Server 2012 + (format works differently for time and datetime - datetime is required here)
  , case when len(convert(varchar(32),@Time,109)) > 17 then convert(varchar(2),@Time,109) else '0' + convert(varchar(1),@Time,109) end [Convert Hour] -- SQL Server pre-2012
  -- If you ultimatly want the hours and minutes together then do it as one
  , format(cast(@Time as datetime),N'hh\:mm') [Format hour:min] -- SQL Server 2012 + (format works differently for time and datetime - datetime is required here)
  , case when len(convert(varchar(32),@Time,109)) > 17 then convert(varchar(5),@Time,109) else '0' + convert(varchar(4),@Time,109) end [Convert hour:min] -- SQL Server pre-2012
  -- And if you want am/pm
  , format(cast(@Time as datetime),N'hh\:mm tt') [Format hour:min tt] -- SQL Server 2012 + (format works differently for time and datetime - datetime is required here)
  , case when len(convert(varchar(32),@Time,109)) > 17 then convert(varchar(5),@Time,109) else '0' + convert(varchar(4),@Time,109) end -- SQL Server pre-2012
  + case when @Time >= '12:00:00' then ' PM' else ' AM' end [Convert hour:min tt] -- SQL Server pre-2012

Returns:

Original            Format Hour Convert Hour    Format hour:min Convert hour:min    Format hour:min tt  Convert hour:min tt
21:33:44.0000000    09          09              09:33           09:33               09:33 PM            09:33 PM

If you were interested in adding/subracting dates then use dateadd.

Format time using 12 hour notation

2 Answers2