Post reply

Datetime data type

  • January 13, 2012 at 2:58 pm

    #262818

    I have a data set with a date column that looks like this: '25-Apr-84'. I would like to convert it to a datetime data type, but I get the error:

    Msg 241, Level 16, State 1, Line 2

    Conversion failed when converting date and/or time from character string.

    when I try. I have tried several types of convert and get the same error. And yes, I have looked at BOL. Does anyone know how to handle this date?

    Thank you, Amy

  • January 13, 2012 at 3:03 pm

    #1433554

    What is the code you are running? When I run this

    select CONVERT(datetime, '25-Apr-84')

    I get the result 1984-04-25 00:00:00.000. Is that not what you want?

  • January 13, 2012 at 3:05 pm

    #1433555

    I tried this select cast('25-Apr-84' as datetime)

    and it returned this - 1984-04-25 00:00:00.000

    If you're trying this against the whole table, it could be that you have some data isn't a vaild date. You try querying for isdate(yourdatecolumn) = 0.

  • January 13, 2012 at 3:11 pm

    #1433563

    When running isdate the only records with a '0' are NULL columns. I wouldn't think that's what's messing it up. Well, aparently that was the problem, since when I ran CONVERT(datetime, [Column 5]) with a WHERE statement of ISDATE([Column 5]) = 1 I get the formated dates just like I wanted. Interesting.

    Thank you! I'll try and remember about the ISDATE function.

  • January 13, 2012 at 3:15 pm

    #1433567

    HA! This is funny. When the table was imported, instead of null values, the word "NULL" was actually inserted into the records.

  • January 13, 2012 at 3:18 pm

    #1433569

    Strange. Something like this should convert your whole column then if that is the case.

    select convert(datetime, nullif(yourCol, 'NULL'))

  • January 13, 2012 at 4:29 pm

    #1433599

    Amy.G (1/13/2012)

    When running isdate the only records with a '0' are NULL columns. I wouldn't think that's what's messing it up. Well, aparently that was the problem, since when I ran CONVERT(datetime, [Column 5]) with a WHERE statement of ISDATE([Column 5]) = 1 I get the formated dates just like I wanted. Interesting.

    Thank you! I'll try and remember about the ISDATE function.

    Be sure to read BOL for the effects of DATEFORMAT and LANGUAGE SETTINGS (in particular us_english and english) and how these setting can / may effect the return from the ISDATE function

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • January 13, 2012 at 11:09 pm

    #1433650

    ISDATE certainly isn't perfect:

    SELECT

    ISDATE('200801'),

    CAST('200801' AS datetime) /* 2020-08-01 */

    Among the improvements in SQL Server 2012 is the new TRY_PARSE function:

    SELECT

    ISDATE('200801'), /* 1 */

    TRY_PARSE('200801' AS datetime USING 'en-US') /* NULL */

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 14, 2012 at 12:45 am

    #1433660

    It’s not funny, its headache actually. I have seen this issue couple of times in import when NULL values appear as ‘NULL’ string. It very much depends upon which source data (Access, Excel, csv etc.). There is no other way except clean the data manually.

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

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