Post reply

datetime conversion

  • March 1, 2004 at 7:05 pm

    #161514

    Hi,

    I need some help with SQL.

    I want to convert a datetime value into a string, however, when I use the CAST function, it returns, eg,  Jan 1 1900, when I actually want '1900-01-01' with the quotes.

    Any ideas?

    tks.

     

  • March 1, 2004 at 7:09 pm

    #496569

    Convert and string concatenation can help you.

  • March 1, 2004 at 8:31 pm

    #496573

    try this

     

    DECLARE @YEAR CHAR(4), @MONTH VARCHAR(2), @DAY VARCHAR(2)

    SET @YEAR = CAST(YEAR(GETDATE()) AS CHAR(4))

    SET @MONTH = CAST(MONTH(GETDATE()) AS VARCHAR(2))

    IF LEN(@MONTH) = 1 SET @MONTH = '0' + @MONTH

    SET @DAY = CAST(DAY(GETDATE()) AS VARCHAR(2))

    IF LEN(@DAY) = 1 SET @DAY = '0' + @DAY

    SELECT '''' + @YEAR + '-' + @MONTH + '-' + @DAY + '''' AS DATE

  • March 1, 2004 at 9:01 pm

    #496575

    Actually, I think you're using the wrong function... in most cases, CAST and CONVERT work the same way...
    ...except for dates and then CONVERT works very nicely for converting DateTime values to strings.
    CONVERT looks like this...
     
    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
       (see "Books-on-Line" for a table of what "style" a datetime expression can be when converting DateTime "expressions" to CHAR or VARCHAR)
     
    To convert to the format that you want, try this...
    CONVERT(Varchar(30), yourdatetimevalue, 120)  but that will also give you the time 
     
    But, that's not a problem either...
    LEFT(CONVERT(Varchar(30), yourdatetimevalue, 120),10) but that doesn't give you the quotes you want
     
    ''''+LEFT(CONVERT(Varchar(30), yourdatetimevalue, 120),10)+'''' should do the trick
     
    And, "YES", those are four single quotes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 1, 2004 at 9:12 pm

    #496577

    I tried the CONVERT function, and it didn't seem to work...

    Thanks Jeff, your last suggestion, this works great.

     

  • March 1, 2004 at 11:52 pm

    #496593

    Statement below does it "in one".  The part shown in red is the date to convert to the format you want. Change the whole of the portion shown in red by your datetime variable - don't be put off by it being an expression in my sample code - I could have used a separate "declare" and "set".

    select ''''+substring(convert(varchar,convert(datetime,'1 Feb 1990'),120),1,10)+''''
    '1990-02-01'

  • March 2, 2004 at 4:09 am

    #496627

    How about this, we can get rid of the substring as well.

    select ''''+convert(varchar(10),convert(datetime,'1 Feb 1990'),120)+''''

    Or say for current day

     

    select ''''+convert(varchar(10),convert(datetime,GETDATE()),120)+''''

     

     

    Prasad Bhogadi
    www.inforaise.com

  • March 3, 2004 at 8:33 am

    #496943

    try select '''' + convert(varchar(10),getdate(),120) + ''''

     

    or

    select  convert(varchar(10),getdate(),120)  to get the date without quotes

  • March 3, 2004 at 8:56 am

    #496965

    Yeah should absolutely solve it without any issues whatsoever.

     

    Prasad Bhogadi
    www.inforaise.com

  • March 3, 2004 at 2:57 pm

    #497088

    Sql Server also a neat function QuoteName() that can help clean up all the single quotes.  You could use Select QuoteName(convert(varchar(10),getdate(),120), '''')which will return '2004-03-03'.  It is a little easier to read.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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