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:


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?


2 Answers 2


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
                          THEN CAST(SUBSTRING(string_field,7,2) AS INT)+2000
                          ELSE CAST(SUBSTRING(string_field,7,2) AS INT)+2100
                     CAST(SUBSTRING(string_field,4,2) AS INT),
                     CAST(SUBSTRING(string_field,1,2) AS INT)) AS date_interpreted_as_RR, ...
  • 1
    @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...
    – Akina
    Aug 7, 2019 at 7:17

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.

  • While I agree with you, I have no choice to switch for now.
    – Uwe Keim
    Aug 8, 2019 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.