convert time from 1530 into 3:30 PM Time Format SQL Server

Question

Trying to convert time from 1530 into 3:30 PM time format in SQL server. Times = 1530

I have tried:

convert(time,LEFT(Times,2)+':'+right(Times,2))

Results: 15:30:00.0000000

But it is in 24 Hrs, I want to convert into 3:30 PM. And Remove seconds. Any ideas?

Thanks in advance.

Is 1530 a string or INT. What would 9:30 AM look like 0930 or 930 ? — John Cappelletti, Mar 14 '19 at 14:47
Possible duplicate of [time format in SQL Server](https://stackoverflow.com/questions/3877907/time-format-in-sql-server) — Juan Carlos Oropeza, Mar 14 '19 at 14:48
[Time has no format](https://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028). If you want a specific format, you need a string, not a time. — Zohar Peled, Mar 14 '19 at 15:15

score 2 · Answer 1 · answered Mar 14 '19 at 14:51

2

If you want to format it you can use Format or convert

SELECT FORMAT(GETDATE(), 'HH:MM')

SELECT CONVERT(VARCHAR(10), GETDATE(), 108)

Convert details: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

answered Mar 14 '19 at 14:51

Brad

3,454
3
27
50

This addresses only the final format portion. It does not answer the conversion question. Furthermore, format() should be used sparingly. – John Cappelletti Mar 14 '19 at 15:06
Furthermore HH will give you 24 hour time. MM will give you MONTH not minute and you forgot tt for AM/PM – John Cappelletti Mar 14 '19 at 15:09

UnhandledExcepSean · Answer 2 · 2019-03-14T15:06:43.943

If you are running an older version of SQL Server, this will work:

DECLARE @times TABLE (
    String VARCHAR(20),
    Numeral TIME,
    FriendlyString VARCHAR(20)
)

INSERT INTO @times (String) SELECT '0001'
INSERT INTO @times (String) SELECT '1200'
INSERT INTO @times (String) SELECT '2359'

UPDATE @times
SET Numeral=CONVERT(VARCHAR(20),CONVERT(INT,String)/100) + ':' + RIGHT('00' + CONVERT(VARCHAR(20),CONVERT(INT,String)%100),2)
    ,FriendlyString=
        CONVERT(VARCHAR(20), CASE WHEN (CONVERT(INT,String)/100)%12 = 0 THEN 12 ELSE (CONVERT(INT,String)/100)%12 END ) + ':' +
        RIGHT('00' + CONVERT(VARCHAR(20),CONVERT(INT,String)%100),2) + ' ' +
        CASE WHEN (CONVERT(INT,String)/100)>=12 THEN 'PM' ELSE 'AM' END

select *
from @times

This is amazing in how its constructed to convert times. – Tarak Pandya Mar 14 '19 at 15:39 — Tarak Pandya, Mar 14 '19 at 15:39

score 0 · Accepted Answer · answered Mar 14 '19 at 14:55

0

try this:

SET @Times ='1530'  
select CONVERT(varchar(15),CAST(CONVERT(datetime,LEFT(@Times,2)+':'+right(@Times,2)) AS TIME),100)

answered Mar 14 '19 at 14:55

Hasan Mahmood

978
7
10

1

FYI, this doesn't work if a <4 character string is provided – UnhandledExcepSean Mar 14 '19 at 15:42
@UnhandledExcepSean you can update the solution to work for times formatted like 445 too by replacing @Times by `RIGHT( '0000' + @Times, 4 )`. This makes @Times a 4 character string (0445) and it reads like a march song: `left( right () )` :) – GerardV Jul 18 '23 at 14:44

score 0 · Answer 4 · answered Mar 14 '19 at 15:13

If your working from a string of 'hh:mm' then...

Declare @t VarChar(15) = '15:30'

Declare @h Int = Left(@t,2)
Declare @m Int = Right(@t,2) 


Select 
Case When @h > 11 Then Cast((iif(@h=12,24,@h) - 12) As VarChar(15)) + ':' + Cast(@m As VarChar(2)) + ' PM' Else @t + ' AM' End As time

convert time from 1530 into 3:30 PM Time Format SQL Server

4 Answers4