Convert a DDMMYY Text Date to DATETIME in SQL Server

sqlSQLtipssql-serverdatetime

There is no built-in format for DDMMYY text such as '200121', so put the slashes in first with STUFF and then convert it with CONVERT style 3 (dd/mm/yy).

-- 1) Put the slashes in: DDMMYY becomes DD/MM/YY
SELECT STUFF(STUFF('200121', 3, 0, '/'), 6, 0, '/');
-- Result: 20/01/21

-- 2) Convert it to a date and time (style 3 = dd/mm/yy)
SELECT CONVERT(DATETIME, STUFF(STUFF('200121', 3, 0, '/'), 6, 0, '/'), 3);
-- Result: 2021-01-20 00:00:00.000

Comments

Loading comments…