T-SQL - Convert TEXT to DATE

Rickzzz
Rickzzz used Ask the Experts™
on
column DateOfBirth is varchar(08)
I need to convert that text to a date.

There are no leading 0's in that column, so Sept 20 1980 is stored as '9201980'

I need to convert that text to a proper date, or '09/20/1980'
declare @DateOfBirth varchar(08) = '9201980'
select CONVERT(varchar,CONVERT(datetime,@DateOfBirth ,  112),121)  -- This and several variances have failed

Open in new window


Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
So how do you know if 1111980 is Nov. 1st 1980 or January 11th 1980?
Database Administrator
Top Expert 2005
Commented:
Assuming the lack of leading zero is only for the month try this...

DECLARE @DOB	VARCHAR(8) = '9201980'

SELECT CONVERT(DATE, STUFF(STUFF(RIGHT('0' + @DOB, 8), 3, 0, '/'), 6, 0, '/'), 101)

Open in new window

Jason clarkDBA Freelancer

Commented:
If your column is really text you need to convert to varchar before converting to datetime
select convert(datetime, convert(varchar(30), Remarks), 101)

Open in new window

Go this one it may be helpful to you.
http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT CAST(RIGHT(DateOfBirth, 4) + LEFT(RIGHT('0' + DateOfBirth, 8), 4) AS date) AS BirthDate
from (
    select '9201980' as DateOfBirth union
    select '1111980'
) as test_data

Author

Commented:
Thanks Brian, perfect.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial