Oracle 19c - Zero padded year-CodePudding

I have some zero padded year DATE column values (e.g. 0018-04-30), but not all values. Most are 4 digit years (e.g. 2022). What's the most efficient way of converting to a year with century (i.e. 2018-04-30).

I'm not a Oracle guy, but what I came up with is to use a CASE statement with EXTRACT and ADD_MONTHS functions:

CASE 
    WHEN EXTRACT(YEAR FROM DateColumn) < 1000 THEN ADD_MONTHS(DateColumn,(12*2000)) 
    ELSE DateColumn 
END

Example:

SELECT
    TO_CHAR(
        CASE 
            WHEN EXTRACT(YEAR FROM (DATE '18-04-30')) < 1000 THEN ADD_MONTHS((DATE '18-04-30'),(12*2000)) 
            ELSE (DATE '18-04-30') 
        END,'YYYY-MM-DD') AS CorrectedDate
FROM DUAL ;

Is there a better, more efficient, way of doing this?

CodePudding user response：

Use an INTERVAL literal to add 2000 years to your dates:

SELECT dt   INTERVAL '2000' YEAR(4)
  FROM DATE_TABLE

db<>fiddle here

Also, your CASE expression can be a bit simpler:

CASE 
  WHEN DateColumn < DATE '1000-01-01' THEN DateColumn   INTERVAL '2000' YEAR(4)
  ELSE DateColumn 
END

CodePudding user response：

You may remove leading zeroes from the date using ltrim function and use RR date format element to convert the result to a date, which was introduced for y2k problem (I presume this is the source of such dates):

with dates(dt) as (
  select *
  from sys.odcidatelist(
    date '0018-04-30',
    date '3018-05-30',
    date '0070-12-29'
  )
)
select
  to_date(
    ltrim(to_char(dt, 'yyyy-mm-dd'), '0'),
    'rr-mm-dd'
  ) as dt
from dates;

| DT         |
| :--------- |
| 2018-04-30 |
| 3018-05-30 |
| 1970-12-29 |

db<>fiddle here