none
How can I convert a DateTime to a custom string

    Question

  • I need to convert a DateTime value to a custom string format, but not sure how to do this.

    The format I need is: mm/dd/yyyy+hh:mm:ss+AM (or PM).

    I will also need the plus signs.

    Wednesday, October 12, 2011 3:07 PM

Answers

All replies

  • HI Bill !

    Do you need something like this;


    SELECT CONVERT(VARCHAR,GETDATE(),101) + '+' + CONVERT(VARCHAR,GETDATE(),14) 
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Wednesday, October 12, 2011 3:15 PM
    Answerer
  • Check function CONVERT in BOL. May be there is no an exact style, but you will be able to gather from different ones.

    SELECT CONVERT(varchar(10), GETDATE(), 101) + ' ' + STUFF(RIGHT(CONVERT(varchar(35), GETDATE(), 109), 14), 9, 4, ' ') AS dt; GO

     

     


    AMB

    Some guidelines for posting questions...

    Wednesday, October 12, 2011 3:15 PM
    Moderator
  • This is close, but it has an "11" in the middle rather than '+'

    10/12/2011 11  8:20:57 AM

    This should look like 10/12/2011+8:20:57+AM

    Wednesday, October 12, 2011 3:22 PM
  • This is a bit tricky, but here it is:

     

    DECLARE @d datetime = DATEADD(HOUR,8,CURRENT_TIMESTAMP)
    SELECT CONVERT(VARCHAR(10),@d,101) + '+' + LTRIM(STUFF(RIGHT(' ' + CONVERT(VARCHAR(30),@d,109),14),9,4,'+'))
    

    The idea is based on this blog post

    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

    • Edited by Naomi NModerator Wednesday, October 12, 2011 3:31 PM
    • Marked as answer by Bill2010 Wednesday, October 12, 2011 3:33 PM
    Wednesday, October 12, 2011 3:23 PM
    Moderator
  • You got the idea. now play and adapt it to your needs.

    SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 101) + ' ' + STUFF(RIGHT(CONVERT(varchar(35), GETDATE(), 109), 14), 9, 4, ' '), ' ', '+') AS dt;
    GO
    
    /*
    
    10/12/2011+11:25:37+AM
    
    */
    

     

     


    AMB

    Some guidelines for posting questions...

    Wednesday, October 12, 2011 3:26 PM
    Moderator