T-SQL - Convert TEXT to DATE

Rickzzz used Ask the Experts™
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.
Watch Question

Do more with

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

So how do you know if 1111980 is Nov. 1st 1980 or January 11th 1980?
Database Administrator
Top Expert 2005
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

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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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


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