oracle database how to convert consecutive number to date

the following query output a consecutive number of the day from a date, for this example the number 2415021:

SELECT
  TO_NUMBER(TO_CHAR(TO_DATE('01.01.2101', 'DD.MM.YYYY'), 'j'))
FROM DUAL

how i can convert it back to a date with desired format? i can't find anything for this.

i have already tried different variations fo to_date but nothing runs.

please note it is not a number like 20230101 that i want convert to '2023/01/01' it is a consecutive number.

Solution

The 'j' date format element is:

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

You can convert back to a date with the same element:

SELECT
  TO_DATE(TO_CHAR(2488435), 'j')
FROM DUAL

TO_DATE(TO_CHAR(2488435),'J')
01-JAN-01

That gives you a value with a DATE data type. That has an internal binary representation, and does not have any inherent human-readable format. Your client generally decides how to display the internal representation, often using NLS settings, e.g.:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

SELECT
  TO_DATE(TO_CHAR(2488435), 'j')
FROM DUAL

TO_DATE(TO_CHAR(2488435),'J')
2101-01-01 00:00:00

If you want a string value in a specific format then explicitly convert it:

SELECT
  TO_CHAR(TO_DATE(TO_CHAR(2488435), 'j'), 'DD.MM.YYYY')
FROM DUAL

TO_CHAR(TO_DATE(TO_CHAR(2488435),'J'),'DD.MM.YYYY')
01.01.2101

Or for your example number, which doesn't match the date in your code:

SELECT
  TO_CHAR(TO_DATE(TO_CHAR(2415021), 'j'), 'DD.MM.YYYY')
FROM DUAL

TO_CHAR(TO_DATE(TO_CHAR(2415021),'J'),'DD.MM.YYYY')
01.01.1900

fiddle

But don't store or pass around dates formatted as strings, or as numbers; store and use them as dates, and only format them for final display or an external call (e.g. formatting in ISO format in JSON API call etc.) where a string is required.