Home How to get next available year from DD-MON date format and have it in DD-MM-YYYY format?

Questions

How to get next available year from DD-MON date format and have it in DD-MM-YYYY format?

June 27, 2023

How do I add the next available year to the following date format first. So for example if the date is July and today it is June it will be 2023. But if the date is January and today is June then it will be 2024 because January has already passed. The dates are in a very strange format of DD-MON and the system I need to upload to won’t take this format and required the correct year depending on the date.

Select '07-Jul'
From sys.dual

Select '08-Oct'
From sys.dual

The desired result will be 07-07-2023 and 08-10-2024.

I am using Oracle SQL

>Solution :

Use TO_DATE, a CASE expression and ADD_MONTHS:

SELECT CASE
       WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
       THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
       ELSE ADD_MONTHS(
              TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
              12
            )
       END
FROM   dual

If you want the output as a formatted string (rather than as a DATE, which is a binary data-type and has no format) then you can wrap it in TO_CHAR:

SELECT TO_CHAR(
         CASE
         WHEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English') >= SYSDATE
         THEN TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English')
         ELSE ADD_MONTHS(
                TO_DATE('07-Jul', 'DD-Mon', 'NLS_DATE_LANGUAGE=English'),
                12
              )
         END,
         'DD-MM-YYYY'
       )
FROM   dual