Solved

Convert VARCHAR to Datetime

Posted on 2011-09-12
10
488 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
  • 7
  • 3
10 Comments
 
LVL 59

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 59

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
 
LVL 59

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 59

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 59

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 59

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 59

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now