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

Rahul Polaboina 181 Reputation points
2023-05-29T05:33:00.7166667+00:00

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
    2023-05-30T04:37:17.4566667+00:00

    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
    2023-05-29T06:25:25.36+00:00

    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.

    0 comments No comments

  3. Olaf Helper 43,166 Reputation points
    2023-05-30T06:04:06.3433333+00:00

    17: 00 PM

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

    0 comments No comments