Equivalent of Oracle RR date format in SQL Server

Question

Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server.

The Oracle export has date formats like this:

to_date('01.02.28','DD.MM.RR')

The RR means that values between 0 and 49 assume the current century, whereas 50 to 99 asume the previous century.

I currently see no SQL Server equivalent to that notation.

The only solution I can think about is to write a quick script that does some Regex and/or string operations to detect the numeric values and add the century by myself so that I could use YYYY for SQL Server.

My question:

Is there a way to express RR in T-SQL?

• Uwe Keim: I've now written this small C# console application to modify my scripts.

Answer 1

My question:

Is there a way to express RR in T-SQL?

It can be done, for example, as

-- from date to string
SELECT FORMAT(datetime_field, CASE WHEN YEAR(datetimefield) > 49 
 THEN 'yyyy-MM-dd' 
 ELSE 'yy-MM-dd' 
 END) AS date_formatted_as_RR, ...

---

-- from string to date
SELECT DATEFROMPARTS(CASE WHEN CAST(SUBSTRING(string_field,7,2) AS INT)>49
 THEN CAST(SUBSTRING(string_field,7,2) AS INT)+2000
 ELSE CAST(SUBSTRING(string_field,7,2) AS INT)+2100
 END,
 CAST(SUBSTRING(string_field,4,2) AS INT),
 CAST(SUBSTRING(string_field,1,2) AS INT)) AS date_interpreted_as_RR, ...

• Akina: @UweKeim The last query (from string to date) have a wide list of possible realizations... you may use conditional REPLACE(), STRING_SPLIT() instead of SUBSTRING(), etc...

Answer 2

The best solution would be to ditch the RR (or RRRR) notation completely. It was meant as a short-term, kick the can down the road, fix to buy some time remediating for Y2k. That was TWENTY YEARS ago. Long, long past time to wean ourselves it and simply start using 4-digit years in everything we do.

• Uwe Keim: While I agree with you, I have no choice to switch for now.