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

Rahul Polaboina 181 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,327 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Chandu_0124 11 Reputation points

    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'); 

    User's image

    1 person found this answer helpful.
    0 comments No comments

  2. LiHongMSFT-4306 25,651 Reputation points

    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
    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.

    0 comments No comments

  3. Olaf Helper 43,166 Reputation points

    17: 00 PM

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

    0 comments No comments