471,872 Members | 1,093 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,872 software developers and data experts.

Date Conversion Problem From varchar to Datetime

But the problem is we have dates in Varchar instead Datetime

While Converting Varchar To Datetime All four formats are unable to



select Convert(Datetime,'18-11-2008 2:35:19 PM',102) -- error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

select Convert(Datetime,'11-18-2008 2:35:19 PM',102) ok

select Convert(Datetime,'18/11/2008 2:35:19 PM',102) Error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

select Convert(Datetime,'11/18/2008 2:35:19 PM',102) ok
Nov 19 '08 #1
1 8185
2,878 Expert 2GB
Standardize your date data first. You can not just use convert. As I said you'll have problems with a valid date string but converted differently.

If I'm to do it, i'll do this:

1. Add a new column to hold the new date string.
2. All string that starts with integer that are greater than 1000 and the next integer (consider the separator) is greater than 12, that's parse your datestring as YYYY/MM/DD and store it on your new column in YYYYMMDD format.
3. If the first integer is greater than 12 and the next is less than 13, parse your string as DD/MM/YYYY and store it as YYYYMMDD
4. Analyze those rows with newcolumn is null. This has to be manually checked, though. Your SQL Server can not figure out for yourself if 01/10/2008 is January 10 or October 1.If you simply use CAST or CONVERT it will use your sql server setting to convert it.
5. Once new column is fully populated, you may then analyze the values, add another smalldatetime/datetime column and populate it with the converted one. Up to you if you will delete those temporary columns. I'm just recommending you create them so that you don't modify the original one.

Good luck!

-- CK
Nov 19 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Sandy Fleming | last post: by
4 posts views Thread by Annie D via AccessMonster.com | last post: by
4 posts views Thread by Phillip Vong | last post: by
reply views Thread by NeoPa | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.