Why does the year format incorrectly with leading zeros in Oracle SQL date formatting?

Question

Relatively new to SQL. I'm trying to combine several date columns - one for the full date (OPR_DATE), one for the hour of the date (HOUR_ENDING), and one for the minute of that date (OPR_5MIN), and create a new column with the full correct date/hour/minute (VALID_DATE).

I'm able to correctly add the month, day, hour, and minute, but the year reads a 0 for the thousands digit instead of a 2. Ex: 2022 in OPR_DATE is formatted to 0022 in VALID_DATE.

As an example, for the following code, where OPR_DATE is 2/24/2022 12:00:00 AM, HOUR_ENDING is 1.00000, and OPR_5MIN is 5.0000, VALID_DATE is formatted as 2/24/0022 01:05:00 AM.

OPR_DATE is a Datetime, and HOUR_ENDING and OPR_5MIN are Fixed Precision Numbers.

SELECT to_char(to_date(r.OPR_DATE, 'fmmm/fmdd/yyyy HH:MI:SS PM') + r.HOUR_ENDING/24 + r.OPR_5min/(24*60), 'fmmm/fmdd/yyyy HH:MI:SS PM') AS VALID_DATE,  
r.OPR_DATE, r.HOUR_ENDING, r.OPR_5MIN, r.DATA_ITEM, r.RESOURCE_NAME, r.VALUE AS REAL_TIME_VALUE, d.VALUE as DAY_AHEAD_VALUE 
FROM ZE_VIEW.MRTU_SLD_FCST_RTM_V  r
JOIN ZE_VIEW.MRTU_SLD_FCST_DAM_V d
ON d.OPR_DATE = r.OPR_DATE AND d.HOUR_ENDING = r.HOUR_ENDING

Additionally, note that in the code above, I need to cast VALID_DATE as fmmm/fmdd/yyyy to make the code work, while OPR_DATE is in fmdd/fmmm/yyyy format. If I cast VALID_DATE to the date format like OPR_DATE is in natively (fmmm/fmdd/yyyy), I get the following error:

"Oracle database error 1858: ORA-01858: a non-numeric character was found where a numeric was expected

Error Code: 3834A5EA"

Answer 1

Your ZE_VIEW.MRTU_SLD_FCST_RTM_V.OPR_DATE column appears to be a DATE data type.

NEVER use TO_DATE on a DATE as TO_DATE expects a string as the first argument and so Oracle will implicitly convert the DATE to a string and then pass it to the function to convert back to a DATE; at best, this is pointless and, at worst, Oracle will use a different format in the implicit string conversion and your date will be mangled (which is what is happening for you as the date is being implicitly converted to a string with a two-digit year and then converted back expecting a four-digit year).

SELECT to_char(
         r.OPR_DATE + r.HOUR_ENDING/24 + r.OPR_5min/(24*60),
         'mm/dd/yyyy HH:MI:SS PM'
       ) AS VALID_DATE,  
       r.OPR_DATE,
       r.HOUR_ENDING,
       r.OPR_5MIN,
       r.DATA_ITEM,
       r.RESOURCE_NAME,
       r.VALUE AS REAL_TIME_VALUE,
       d.VALUE as DAY_AHEAD_VALUE 
FROM   ZE_VIEW.MRTU_SLD_FCST_RTM_V  r
       JOIN ZE_VIEW.MRTU_SLD_FCST_DAM_V d
       ON     d.OPR_DATE = r.OPR_DATE
          AND d.HOUR_ENDING = r.HOUR_ENDING