Solved

Convert VARCHAR to Datetime

Posted on 2011-09-12
10
521 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:jsuanque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526468
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526498
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
 

Author Comment

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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526516
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
 

Author Comment

by:jsuanque
ID: 36526522
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526573
That means that your default language is set to English where date format is MM/DD/YYYY.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526592
My real question to you was when encountering 10/1/2004 should I interpret this as Oct 1 or Jan 10.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526601
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
 

Author Comment

by:jsuanque
ID: 36526748
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 125 total points
ID: 36526779
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question