Convert VARCHAR to Datetime

Hello Experts,

Need your expertise on this one...I need to convert the DOB column on one of our tables from Varchar(10) to datetime but the problem the columns data is a bit unorganised..

DOB
10/1/98
27/08/1999
1/04/01
21/1/2000

Can you show me how to convert it without hitting a problem or error ...out-of-range)
 since all i tried encountered an error one way or the other.
jsuanqueAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Try this:
SET LANGUAGE 'British English';

SELECT CONVERT(DATETIME, DOB, CASE CHARINDEX('/',REVERSE(DOB)) WHEN 3 THEN 3 ELSE 103 END)
FROM your_table
WHERE ISDATE(DOB) = 1
;

SET LANGUAGE 'English';

Open in new window


The, run this to find out the values that are not included:

SET LANGUAGE 'British English';

SELECT DOB
FROM your_table
WHERE ISDATE(DOB) = 0
;

SET LANGUAGE 'English';

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
It is because your dates appear to be in DD/MM/YYYY format in some places, use CONVERT with format code 103. E.g. Convert(Datetime, YourColumn, 103)
0
 
Kevin CrossChief Technology OfficerCommented:
Hmm. I am on a mobile device, so didn't catch the variation before. If you run into trouble let me know. You can always test a column first using IsDate() to tell if it will parse "as-is".
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jsuanqueAuthor Commented:
Hello MWVisa1,
Already did try that one and was getting ."Conversion failed when converting datetime from character string." error message.

0
 
Kevin CrossChief Technology OfficerCommented:
Yes, probably for the ones with two digit years...can you confirm all are in DD/mm/YYYY format. If so, you can just use a case statement to switch the format code between 3 and 103.
0
 
jsuanqueAuthor Commented:
Helo MWVisa1,

The column has mixed date and a non date after testing the column. For some reason based on the test output some values such as 22/12/2003 returned a 0 result while 11/11/2004 returned 1.
0
 
Kevin CrossChief Technology OfficerCommented:
That means that your default language is set to English where date format is MM/DD/YYYY.
0
 
Kevin CrossChief Technology OfficerCommented:
My real question to you was when encountering 10/1/2004 should I interpret this as Oct 1 or Jan 10.
0
 
Kevin CrossChief Technology OfficerCommented:
Try this:

SELECT CONVERT(DATETIME, DOB, CASE CHARINDEX('/',REVERSE(DOB)) WHEN 3 THEN 3 ELSE 103 END)
FROM your_table
;

If the last slash is 3 characters away from the end, it uses format 3; otherwise, 103.
0
 
jsuanqueAuthor Commented:
Still getting thise error message...
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.