By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Date Conversion Problem From varchar to Datetime

P: 37
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
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,878
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.