Change Two digit year to 4 digit year with correct century (not allowed to modify two year cutoff date option)

Question

I tried to do a little research on this, but didn't have much luck.

I have a date (MMDDYY format) stored in a varchar field (DateValue) in a table that looks something similar to this:

TableA

--------------------------------------------------------------------------------------------------------

----------------------                                  
|| ID  |  DateValue ||
  -----  -----------    
|| 1   |  011212    ||
|| 2   |  011549    ||
|| 3   |  070860    ||

--------------------------------------------------------------------------------------------------------

I am trying to add another column ConvertDateValue to the same table which would be a date field. The values in this column would be converted values from DateValue field in this format (YYYY-MM-DD). Here is what I have tried:

--declare table variable
declare @tableA table (ID int, DateValue varchar(50))

--insert sample values into table variable
insert into @tableA VALUES 
(1,'011212'),
(2,'011549'),
(3,'070860')

begin
select ID,
       DateValue,
       CAST(STUFF(STUFF(DateValue, 3, 0, '-'), 6, 0, '-') as date) as ConvertDateValue 
 from @tableA 
end

Output:

---

|| ID  |  DateValue ||  ConvertedDateValue ||
  -----  -----------    ------------------
|| 1   |  011212    ||   2012-01-12        ||
|| 2   |  011549    ||   2049-01-15        ||
|| 3   |  070860    ||   1960-07-08        ||

Expected Output:

---

|| ID  |  DateValue ||  ConvertedDateValue ||
  -----  -----------    ------------------
|| 1   |  011212    ||   2012-01-12        ||
|| 2   |  011549    ||   2049-01-15        ||
|| 3   |  070860    ||   2060-07-08        ||  --Here is the difference 

I need to ensure that the ConvertedDateValue is displaying correct century. The above value could also be 1960-07-08 for some data and not for others.

FYI, I am aware of the two year cutoff date option and we have the default value set for that i.e. year 2049. Unfortunately, I am not allowed to change any of the advanced option settings for various reasons.

I am open to feedback and what alternatives are out there? Thanks in advance!

Answer 1

That's pretty awesome, here is the substring to fix it:

select convert(date, cast(substring(DateValue,1,2) + '/' +  substring(DateValue,3,2) + '/20' + substring(DateValue,5,2) as datetime), 101 ) as converteddate

The convert isn't really necessary to populate the db, but it's the format you asked for.

Answer 2

Just manually parse the date and use a CASE statement to determine the century. If you want '070860' to sometimes be 2060-07-08, and other times to be 1960-07-08 then what logic besides the 2 digit year do you have to determine the century?

--declare table variable
declare @tableA table (ID int, DateValue varchar(50))
DECLARE @Month int
DECLARE @Day int
DECLARE @Year int


--insert sample values into table variable
insert into @tableA VALUES 
(1,'011212'),
(2,'011549'),
(3,'070860')

begin

select ID,
       DateValue,
       (CONVERT(varchar(4), CASE 
            WHEN CAST(SUBSTRING(DateValue, 5, 2) AS int) > 88 THEN 1900 + CAST(SUBSTRING(DateValue, 5, 2) AS int)
            ELSE 2000 + CAST(SUBSTRING(DateValue, 5, 2) AS int)
    END)
    + '-' + SUBSTRING(DateValue, 1, 2) + '-' + SUBSTRING(DateValue, 3, 2))
 as ConvertDateValue 
 from @tableA 
end

Output

|| ID  |  DateValue ||  ConvertedDateValue ||
  -----  -----------    ------------------
|| 1   |  011212    ||   2012-01-12        ||
|| 2   |  011549    ||   2049-01-15        ||
|| 3   |  070860    ||   2060-07-08        ||