Conversion from Varchar to DateTime Not Working??

[sconti11]

I am in the middle of a project and then the dreaded Conversion error appears during my INSERT command. I am moving data from one database to another.

Database A has datetime fields that are VARCHAR and look like this: 12/30/2009 3:53:02 PM

The destination table has these fields as datetime. I have tried the following within the INSERT statement:

NSERT INTO A SELECT
CONVERT(VARCHAR(50), CAST(dateAccepted AS DATETIME), 130) [dateAccepted]

...but I am still getting the error.

Any help will be appreciated

 

[SQLBill]

The most common DATETIME conversion error is due to SQL Server not understanding the format. Is the 12 the day or month? Is the 30 the month (not valid) or the day? SQL Server doesn't know unless you tell it.

Try adding this to the beginning of the script:

SET DATEFORMAT MDY
GO

The thing I want to know is why are you CASTing a VARCHAR value to DATETIME and then CONVERTing it back to VARCHAR?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[simian336]

Database (table I assume) A has datetime fields that are VARCHAR

Then you

INSERT INTO A

Are you trying to insert into the the original table?

Simi

 

[sconti11]

The 12 is the month and 30 is the day.

I thought the code I had, was actually converting to VARCHAR then to DATETIME???

Is it possible that the date format is invalid within the source table?

 

[gmmastros]

Nope.

Your code is converting to DateTime first. SQL Server interprets dates based on the default language of the login. In your case, I suspect the default language is one that has dd/mm/yyyy, but your data is mm/dd/yyyy. Since the destination table has a datetime column, you simply need to convert from varchar to date time based on a mm/dd/yyyy interpretation. Something like this...

INSERT INTO A SELECT  
CONVERT(DateTime, dateAccepted, 101) [dateAccepted]

By using the 3rd parameter of the convert function, you are basically telling SQL Server to default to mm/dd/yyyy format.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom