Err msg 241, convert char to datetime daatatype

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

Err msg 241, convert char to datetime daatatype

Author

Topic

sowmyav
Starting Member

34 Posts

Posted - 2009-04-21 : 04:40:45

Hi,
I want to convert the datatype from char to datetime for the reports i prepare.
Bit i am not able to convert .
I am getting the folowing error.

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

But I need to convert the same....
It would be of very great help, if you suggest me the solution for the same.

Thanks in adv,
Sowmya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 04:46:53

It means you have dirty data in your column which stores your dates as char.
Is there a reason you don't use datetime datatype?

E 12°55'05.63"
N 56°04'39.26"

sowmyav
Starting Member

34 Posts

Posted - 2009-04-21 : 05:06:30

Actually, it is a table from the JUmp tool i save to the sqlserver database.It is created as char(10) datatype which is date field, I want to save it as datatime datatype for my next steps.
I have created the same longback, but now I am getting this error.
Could you please let me know how it can be changed?
Thanks,
Sowmya

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-21 : 05:19:40

Hi,
Can you post sample data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-21 : 05:20:13

To get all dirty data, use

SELECT DISTINCT Co1l
FROM Table1
WHERE ISDATE(Col1) = 0

E 12°55'05.63"
N 56°04'39.26"

sowmyav
Starting Member

34 Posts

Posted - 2009-04-21 : 05:27:36

Thanks.
But I want to change the datatype in the table, and not for fetching the rows.
As I am connected remotely to sys, I coudn't take the screen shot.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-21 : 07:32:38

quote:
Originally posted by sowmyav

Thanks.
But I want to change the datatype in the table, and not for fetching the rows.
As I am connected remotely to sys, I coudn't take the screen shot.

You should delete data from the table where date col has invalid dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources