Convert varchar value to time in hh:mm:tt in SQL Server

Question

I have a varchar column with time data example " 1700" , "1400" , I need to convert to 17: 00 PM and 02:00 PM or in the format hh:mm:tt

Answer 1

Hi @Rahul Polaboina

Here is sample SELECT script:

SELECT FORMAT(cast('17:00' as datetime),'hh:mm:tt'); 
SELECT FORMAT(cast('14:00' as datetime),'hh:mm:tt'); 

Answer 2

Hi @Rahul Polaboina

Try this:

DECLARE @Table TABLE (TimeCol VARCHAR(20))
INSERT INTO @Table VALUES('1700'), ('1400')

SELECT TimeCol,FORMAT(CAST(CONCAT(LEFT(TimeCol,2),':',RIGHT(TimeCol,2)) AS datetime2),N'hh:mm tt')AS TimeConvert 
FROM @Table
--OR
SELECT TimeCol,SUBSTRING(TimeCol,1,2)+':'+SUBSTRING(TimeCol,3,2) + CASE WHEN SUBSTRING(TimeCol,1,2) < 12 THEN ' AM' ELSE ' PM' END AS TimeConvert 
FROM @Table

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

17: 00 PM

17: 00 PM, really? I always thought US time are up to 12, not above?