CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
bmsqldev
bmsqldev

Reputation: 2735

Convert Integer value into Hour value (AM / PM) in SQL Server

I have a table with hour values. I need to convert the hour value into AM/PM format, as shown here:

val   hour 
---------------
 9     9:00 AM
19     7:00 PM
10    10:00 AM
19     7:00 PM
14     2:00 PM

I have tried this logic:

declare @timing varchar(5)='15:02'

declare @time time=cast(@timing as time)
select convert(varchar(50),@time, 100)

But this requires val to be converted into varchar. Is there any other way or inbuilt function available in SQL Server to do this?

Upvotes: 2

Views: 16119

Answers (6)

Richard
Richard

Reputation: 11

select CONVERT(varchar(15),CAST( cast(@val as nvarchar) + ':00:00.0000000' AS TIME),100)

Upvotes: 1

LukStorms
LukStorms

Reputation: 29647

Without using FORMAT, this should work on SQL Server 2008 and beyond:

declare @T table (val int);

insert into @T values (0),(9),(12),(19);

select val, 
replace(convert(varchar, CAST(CONCAT(val%24,':00') as time), 109),':00.0000000',' ') as [hour] 
from @T;

Basically, cast the INT to a VARCHAR in the 109 predefined format and clean that up.

Or via using FORMAT after transforming the INT to a DATETIME:

select val, FORMAT(DATEADD(HH,val%24,0),'h:mm tt') as [hour] from @T;

Both methods will return:

val hour
0   12:00 AM
9   9:00 AM
12  12:00 PM
19  7:00 PM

Note that the modulus (val%24) was added. That's just to make it also work for hours above 23.

Upvotes: 1

neer
neer

Reputation: 4082

If it is complex then you can create a function.

CREATE FUNCTION dbo.fn_StringHourToAmPm (@timing VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN        

    DECLARE @time TIME = CAST(@timing AS TIME)
    DECLARE @Output NVARCHAR(50)
    select @Output = CONVERT(VARCHAR(50),@time, 100)

    RETURN @Output
END

Then

SELECT dbo.fn_StringHourToAmPm('15:02') -- 3:02PM

Upvotes: 1

M. Jamshaid Alam
M. Jamshaid Alam

Reputation: 86

Try the following:

declare @timing varchar(5)='14'

declare @time time=cast(case when CHARINDEX(':',@timing)=0 then @timing + ':00' else @timing end as time)
select convert(varchar(50),@time, 100)

Or

Select convert(varchar(50),cast(Cast(val as varchar) + ':00' as time),100) FROM YourTable

Upvotes: 2

mcr
mcr

Reputation: 772

SELECT FORMAT(DATEADD(hh,val,'00:00:00'),'hh:mm tt') 
FROM YourTable

Upvotes: 4

Tyron78
Tyron78

Reputation: 4187

Try the following:

SELECT TIMEFROMPARTS(val,0,0,0,0)
  FROM [table];

Upvotes: 2

Related Questions