error converting datetime from character

[laxcoburn]

Hi,
I am having problems updating a datetime field from a varchar field. The varchar field is populated from an import of data from an export from a progress database. No matter what I do to format the imported date field (to format it for different date formats mm-dd-yyyy , mm/dd/yy, yyyymmdd) I get an the
"Syntax error converting datetime from character string" error message.

I have even created a seperate varchar field called testdt and inserted the value: 2005-05-05 08:00:00.000 into it. if I use

update exptel set invoicedon = cast(testdt as datetime)

I get the error message.
If I use
update exptel set invoicedon = '2005-05-05 08:00:00.000 ' it works fine.

So it doesnt appear to be the date format but rather the the fact that I am referencing a varchar field. I am familure with the cast and convert functions and use them to convert datetime fields to varchars but cannot get it to work for varchar to datetime.

What do I need to do to get cast or convert a varchar field to a datetime field?

Thanx in advance

[Shuja Ali]

Hi,
I am having problems updating a datetime field from a varchar field. The varchar field is populated from an import of data from an export from a progress database. No matter what I do to format the imported date field (to format it for different date formats mm-dd-yyyy , mm/dd/yy, yyyymmdd) I get an the
"Syntax error converting datetime from character string" error message.
Have you checked the data in the Varchar field which you are trying to insert/update in the other table as DateTime..
probably some of the record(s) don't contain a valid date in that VarChar field..

try running this query on the table from which has got VarChar Field

Select CAST(myVarCharFieldName as DateTime) From myFirstTable
If this also gives the same error then that means that there are some records which don't contain a valid date...


PS: I would suggest to change the data type of the field in your first table to DateTime...

[laxcoburn]

Actually, I created and insert statement to populate the testdt field with the same date for all records (2005-05-05 08:00:00.000). So I know they all have dates. Also, I am updating a datetime field in the same table though I wouldnt think this should make a difference.

I just tried converting the testdt field to datetime (it is varchar (50)) and got the same error message.

[Shuja Ali]

Actually, I created and insert statement to populate the testdt field with the same date for all records (2005-05-05 08:00:00.000). So I know they all have dates. Also, I am updating a datetime field in the same table though I wouldnt think this should make a difference.

I just tried converting the testdt field to datetime (it is varchar (50)) and got the same error message. This means that the Data present in your varchar field is not DateTime.. there might be some rows(or even one row) where the field contains characters (other than a date/time).. coz even if it is a blank field it should convert it to '1900-01-01 00:00:00.000'

take a look at the original field.. there shud b one with some kind of data that cannot be converted to datetime.

[laxcoburn]

bingo, they were all wrong. If you look in the first post the query that i used to update the testdt field to make sure they all had a date has an extra space at the end. Gotta love spending half a day sweatin the small stuff. Thanx for the help