Error-converting datetime from character string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error-converting datetime from character string

Author

Topic

jjz
Starting Member

31 Posts

Posted - 2010-01-31 : 04:55:07

Hi

May you kindly assist.

I'm trying to convert a varchar column into datetime, however getting the below msg:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I am convinced that there is something wrong with my data in the column i'm trying to covert however i am unable to see the corrupt data, i tried opening the file in excel but the corrupt data is not visible.

The column i'm trying to covert consist this type of date info

2009/11/18 08:21:32
2010/01/05 07:48:51
2010/01/13 08:18:57
2010/01/20 08:38:32
2009/11/18 08:21:32
2010/01/05 07:48:51
2010/01/13 08:18:57
2010/01/20 08:38:32

Your urgent assistance will be highly appreciated.

Thanx

Kristen
Test

22859 Posts

Posted - 2010-01-31 : 05:07:41

For SQL to make an implicit conversion the dates must be 'yyyymmdd' or 'yyyy-mm-ddThh:00:00'

For any other format of "string" date you need to us an explicit conversion.

Assuming you cannot easily make the dates in 'yyyymmdd' format there are two ways to solve this:

Use

SET dateformat ymd

before the implicit conversion to tell SQL that that is the format of your dates.

You can then do


SET dateformat ymd
SELECT *
FROM MyTable
WHERE IsDate(MyColumn) = 0

to find any dates that will cause error when implicit cast is used - that will find any "bad data"

Or you can use


SELECT CONVERT(datetime, MyColumn, 111)

to explicitly cast them (111 = yyyy/mm/dd format)

Edit: emphasised the key points, for benefit of folk coming along later

jjz
Starting Member

31 Posts

Posted - 2010-01-31 : 06:39:34

Thank you so much, your solution worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources