Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert VARCHAR to Datetime

Posted on 2011-09-12
10
Medium Priority
?
537 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 61

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 61

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

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 61

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 61

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 61

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 61

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

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: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

580 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