1

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?

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
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, ...
| improve this answer | |
  • 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 '19 at 7:17
0

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.

| improve this answer | |
  • While I agree with you, I have no choice to switch for now. – Uwe Keim Aug 8 '19 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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