convert date in string format to date datatype sql [duplicate]

Question

I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the value?

example 02121955 -> 1955-02-12

Answer 1

You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together.

A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.

Answer 2

Try this out:

declare @vardate varchar(100)='02121955'
set @vardate= SUBSTRING(@vardate, 1, 2)+'-'+ SUBSTRING(@vardate, 3, 2)+'-'+SUBSTRING(@vardate, 5, 4)
select CONVERT(date, @vardate) as dataconverted

This should do the job for the whole column

UPDATE myTable
SET columnName = SUBSTRING(columnName, 1, 2)+'-'+ SUBSTRING(columnName, 3, 2)+'-'+SUBSTRING(columnName, 5, 4)
ALTER TABLE myTable
ALTER COLUMN columnName date