CONVERT a text column to date format

I have a column nvarchar(8) that I need to update to a date format, MM/DD/YYYY

Some of the values have 7 characters. the rest have 8 characters as shown below:

Col1

6051998

12061999

In both rows the format is M/DD/YYYY and MM/DD/YYYY respectively. I have tried using CONVERT and CAST but receive the error:

Conversion failed when converting datetime from character string.

I've manage to generate the correct format in a select statement using CASE:

SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

END

FROM Table1

How can perform an update of this column using the UPDATE statement? I've tried the following with no success:

UPDATE dbo.Table1

SET date_updted =(SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

END

FROM Table1)

FROM Table1

If your data is as strongly formated as you say, you should be able to convert to datetime with something like:

Code Snippet

select aDate,
convert(datetime, right(aDate, 4) + left( right('0'+aDate, 8), 4))
as convertedDT
from ( select '6051998' as aDate union all
SELECT '12061999'
) a

/*
aDate convertedDT
--
6051998 1998-06-05 00:00:00.000
12061999 1999-12-06 00:00:00.000
*/

|||

Try something like this:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
DateCol varchar(8)
)

INSERT INTO @.MyTable VALUES ( '6051998' )
INSERT INTO @.MyTable VALUES ( '12061999' )

SELECT convert( datetime, ( stuff( stuff( right( '0' + DateCol, 8 ), 3, 0, '/' ), 6, 0, '/' )), 101 )
FROM @.MyTable

-
1998-06-05 00:00:00.000
1999-12-06 00:00:00.000

|||

Thanks for your responses and I'm sure these methods will work fine, however, my question really was how to use the UPDATE Statement to update the column in my initial post without creating a temp table or column. Maybe I missed something in your responses?

|||

Copy the expression from those select statement...

Code Snippet

UPDATE

dbo.Table1

SET

date_updted =convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4))

FROM

Table1

|||

Maybe:

Code Snippet

UPDATE dbo.Table1
SET date_updted
= convert(varchar(10),convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4)),101)

|||Very Nice! Thanks.