mdarr mdarr - 7 months ago 41
SQL Question

SQL Server 2012 - Convert CYYMMDD date to YYYYMMDD

I have a table with just over 200,000 entries where the date is stored as CYYMMDD where C is the century (currently stored as integers). For example, December 5, 1992 would show up as

1921205
and April 25, 2016 would show up as
2160425
. I need to display these results in a new table in a YYYMMDD format. So for the two examples earlier it would be
19921205
and
20160425
. I have tried using

CONVERT(VARCHAR(8),[DATE], 112)


This doesn't give an error but it also doesn't do the conversion so all my dates still look the same. What am I doing wrong?

Answer

You'll need to split your numeric value as follows:

DATEFROMPARTS( 1800 + FLOOR([DATE]/10000) , FLOOR([DATE]/100)%100, [DATE]%100 )