Datetime data type

Question

Post reply

Datetime data type

Amy.G

SSCrazy

Points: 2305
More actions
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

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

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

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?

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 2

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.

Amy.G SSCrazy Points: 2305 More actions · Answer 3

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.

Amy.G SSCrazy Points: 2305 More actions · Answer 4

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 5

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

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]

Paul White SSC Guru Points: 150467 More actions · Answer 7

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

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.