Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert VARCHAR to Datetime

Posted on 2011-09-12
10
Medium Priority
?
527 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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