Post reply

Convert getdate() to Varchar

  • February 6, 2008 at 10:04 am

    #120570

    Trying to update a timestamp field with getdate(). Field is Varchar (16) and the time needs to be in the following format : YYYYMMDDhhmmssms

    Example: 2008020609435709

    Using the code below to get the second, but in desperate need of how to include the milliseconds in this format.

    Select convert(varchar(8),getdate(),112) -- YYYYMMDD

    +substring(convert(varchar(8),getdate(),108),1,2) --HH

    +substring(convert(varchar(8),getdate(),108),4,2) -- mm

    +substring(convert(varchar(8),getdate(),108),7,2) --ss

    Thanks in Advance for your advice!

  • February 6, 2008 at 10:10 am

    #775701

    Please don't cross post.

    Continue this thread here:

    http://www.sqlservercentral.com/Forums/Topic452328-338-1.aspx

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • February 6, 2008 at 10:25 am

    #775708

    14 is what your looking for -

    select replace(convert(varchar, getdate(),112),'/','') + replace(convert(varchar, getdate(),14),':','')

    Tommy

    Follow @sqlscribe

  • February 6, 2008 at 1:12 pm

    #775755

    Thanks again for all your replies. My next big question is this.. How do I find midnight yesterday in this same format?

    Example:

    This is the Date I Need:

    select dateadd(s,-1,dateadd(d,datediff(d,0,GETDATE()) + 1,0))

    This is the Format I Need that Date In:

    (select substring( replace(replace( replace(replace( convert(varchar,getdate(),121),' ',''),'-',''),':',''),'.',''), 1, 16 ))

  • February 6, 2008 at 1:24 pm

    #775761

    Subtract - i.e. getdate()-1

    select substring(replace(replace( replace(replace( convert(varchar,dateadd(s,-1,dateadd(d,datediff(d,0,getdate()-1) + 1,0)),121),' ',''),'-',''),':',''),'.',''), 1, 16 )

    Tommy

    Follow @sqlscribe

  • February 6, 2008 at 1:32 pm

    #775765

    Thanks Tommy.. That is the correct format but I need midnight-last night (based on getdate()).

    This date...2008-02-06 23:59:59.00 or 2008-02-06 24:00:00.00

  • February 6, 2008 at 1:54 pm

    #775774

    In that case -

    select substring( replace(replace(replace(replace( convert(varchar,dateadd(dd, datediff(dd,0,getdate()-1), 0),120),' ',''),'-',''),':',''),'.',''), 1, 16 )

    Tommy

    Follow @sqlscribe

  • February 6, 2008 at 2:02 pm

    #775777

    It's ugly, but works perfectly.

    You Rock Tommy!

  • February 6, 2008 at 2:04 pm

    #775778

    TJK,

    You may already know this, but I think it needs said anyway. This is one of the many reasons why you don't stored datetime information in varchar columns.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • February 6, 2008 at 2:10 pm

    #775782

    Point taken and I agree completely. My headache comes from working with this field which is part of a vendor supplied application. As I'm sure you know, sometimes it is easier (and much faster) to conform on the smaller things than to attend the 12 meetings it takes to get it changed. 🙂

    Thanks again... I really appreciate the help.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply