Post reply

convert char into datetime

  • August 16, 2007 at 8:14 am

    #118126

    Hello friends , i need your help,  i got a problem to try convert a data varchar into datetime, well i need to convert a field of a table which is formatted in 'ddmmyyy'

    for example

    SELECT convert(datetime,'30122006',105)

    and i got the next error

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    if the datas of field would be separated by a '-' for example '30-12-2006' I have not the problem the query is executed successful but the datas have not the separator sign, i am think to aggregate it  , do you know another idea to solve my problem???

    Thanks for read me and excuse me my bad english Cheers

  • August 16, 2007 at 8:28 am

    #727358

    When trying to convert the character value '30122006', sql is expecting the value to represented in the format yyyymmdd.  I tried your sample and got the same error.  adding the '-' allowed me to properly convert the date, as did reformating the string to yyyymmdd format.

    You may need to reformat your data before doing the conversion.

  • August 16, 2007 at 8:33 am

    #727361

    This works. But be careful in your format as Lynn stated.

    SELECT convert(datetime,'20061230',105)

  • August 16, 2007 at 8:34 am

    #727362

    yes, that seems the only solution, ok i will resign myself to reformate my data

    Thank for read me friend

  • August 16, 2007 at 8:39 am

    #727365

    SELECT CONVERT(DATETIME,STUFF(STUFF('30122006', 3, 0, '-'), 6, 0, '-'), 105)

    Regards,
    gova

  • August 16, 2007 at 9:01 am

    #727380

    Thanks i will try it

  • August 16, 2007 at 9:12 am

    #727386

    gova, so that's why i had trouble using STUFF when I was trying to come up with a conversion solution.  Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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