How to convert date in string format to date datatype sql?

344 Asked by Aalapprabhakaran in SQL Server , Asked on Mar 17, 2023

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 code can I use to convert the value?

example 02121955 -> 1955-02-12

Answered by Aashna Saito

To convert date in string format to date datatype sql -

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

How to convert date in string format to date datatype sql?

Your Answer