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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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
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.
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