locked
Specific datetime format RRS feed

  • Question

  • Is there a datetime format when using convert that will result in...mm/dd/yyyy hh:mm:ss AMPM?
    Tuesday, January 26, 2016 8:00 PM

Answers

  • select format(getdate(),'MM/dd/yyyy hh:mm:ss tt')

    --

    select convert(varchar(10),getdate(),101) +' '+ Stuff(Stuff(convert(varchar(26),getdate(),109),21,4,' '),1,12,'')

    • Edited by Jingyang Li Tuesday, January 26, 2016 8:29 PM
    • Marked as answer by Kirkee Wednesday, January 27, 2016 6:07 PM
    Tuesday, January 26, 2016 8:16 PM
  • FORMAT will do what you want but be aware performance of FORMAT is extremely poor relative to CONVERT. It would be best to offload display formatting to another application layer where possible.
    • Marked as answer by Kirkee Wednesday, January 27, 2016 6:08 PM
    Wednesday, January 27, 2016 2:20 AM

All replies

  • select format(getdate(),'MM/dd/yyyy hh:mm:ss tt')

    --

    select convert(varchar(10),getdate(),101) +' '+ Stuff(Stuff(convert(varchar(26),getdate(),109),21,4,' '),1,12,'')

    • Edited by Jingyang Li Tuesday, January 26, 2016 8:29 PM
    • Marked as answer by Kirkee Wednesday, January 27, 2016 6:07 PM
    Tuesday, January 26, 2016 8:16 PM
  • No for CONVERT function. You may use FORMAT() function in SQL Server 2012 and up.

    See also

    Formatting the time from a datetime or time datatype by using the STUFF function


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Tuesday, January 26, 2016 8:18 PM
    • Proposed as answer by FLauffer Tuesday, January 26, 2016 10:23 PM
    Tuesday, January 26, 2016 8:16 PM
  • FORMAT will do what you want but be aware performance of FORMAT is extremely poor relative to CONVERT. It would be best to offload display formatting to another application layer where possible.
    • Marked as answer by Kirkee Wednesday, January 27, 2016 6:08 PM
    Wednesday, January 27, 2016 2:20 AM
  • Hi Kirkee,

    Here's another workaround.

    SELECT STUFF(CONVERT(CHAR(20), GETDATE(), 22), 7, 2, YEAR(GETDATE()));
    

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    Wednesday, January 27, 2016 3:12 AM
  • SET NOCOUNT ON
    DECLARE @startTime DATETIME = CURRENT_TIMESTAMP
    DECLARE @dts TABLE (datetime DATETIME)
    WHILE (SELECT COUNT(*) FROM @dts) < 10000
    BEGIN
     INSERT INTO @dts (datetime) VALUES
     (CURRENT_TIMESTAMP+(RAND()-RAND()))
    END
    SET NOCOUNT OFF
    PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR) +' to insert'
    SET @startTime = CURRENT_TIMESTAMP
    
    SELECT CONVERT(VARCHAR,datetime,101)+' '+STUFF(REPLACE(RIGHT(CONVERT(VARCHAR,datetime,109),14),' ','0'),9,4,' ')
      FROM @dts
    
    PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR)  +' to select composite'
    SET @startTime = CURRENT_TIMESTAMP
    
    SELECT FORMAT(datetime, 'MM/dd/yyyy hh:mm:ss tt')
      FROM @dts
    
    PRINT CAST(DATEDIFF(MILLISECOND,@startTime,CURRENT_TIMESTAMP) AS VARCHAR)  +' to select format()'


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, January 27, 2016 3:26 PM
  • Thanks for all the great options and advice on performance!
    Wednesday, January 27, 2016 4:50 PM
  • You're welcome!

    Please don't forget to close your thread by marking an answer (even if it's something you post).


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, January 27, 2016 5:14 PM