Zero year in Oracle DB

In my third year at Peter the Great SPbPU, I had an exam in SQL in the Oracle database. The exam consisted of three tasks. This exam was one of the most difficult in all four years of study at the university. Three tasks are given for several hours. If you solved all three problems, then you get a grade of 5, one defect (for example, an extra space) – 4, one problem was solved incorrectly – 3, two defects – 3.

Today I want to talk about one of the tasks that was in this exam.

Something went wrong…

I failed this exam. I was able to pass it only on the second attempt. This was very unexpected for me, but for my parents something beyond the bounds, because I studied at 4 and 5, and this had not happened before.

Using only access to the DUAL table, build an SQL query that returns one column containing the calendar for a given month in a given year:

• the number of the day in the month (in numbers),

• full month name in English in capital letters (uppercase),

• year (four digits),

• full name of the day of the week in English in lowercase (lowercase).

Each “subfield” must be separated from the next by one space. As a result, there should be no leading or trailing spaces. The number of rows returned must exactly match the number of days in the current month. The lines should be ordered by the number of days in the month in ascending order.

The calendar must be generated for any valid Oracle date values. Solve the problem without using Model clauses and recursive With.

An example of the output of the result:

1 MAY 2020 friday

2 MAY 2020 saturday

The task seemed to me the simplest. I will give a request with which I solved this problem.

with input as (
    select to_date(:dt, 'mm.syyyy') dt
    from dual
)
select to_number(to_char(level + dt - 1, 'dd'),'99') ||
       to_char(level + dt - 1, ' MONTH ', 'nls_date_language=american') ||
       to_char(level + dt - 1, 'syyyy ' , 'nls_date_language=american') ||
       to_char(level + dt - 1, 'day', 'nls_date_language=american') calendar
from dual
     join input on 1 = 1
connect by level + dt - 1 <= last_day(dt);

At first glance, everything looks good, but let’s take a look at one interesting point. The valid range for the year is an integer from -4172 to 9999, excluding 0. Take December 1 BC. and January 1 A.D. As a result, we will see that after Wednesday comes Saturday, which means the problem is solved incorrectly.

In the Gregorian and Julian calendars no zero year… Also in Oracle there is no zero year, and -0001 of Oracle corresponds to 2 BC. according to traditional chronology.

--Результат 01/01/0001
select to_date('31.12.-0001', 'dd.mm.syyyy') + 1
from dual; 

Then we continue to create dates that belong to our era, as it was done in the first request. -0002 Oracle will “shift” to the right, that is, it will become 2 BC. in traditional chronology, -0003 Oracle, would become 3 BC. etc. But we cannot also shift the Oracle -0001 year by adding days / months, etc. However, we can find a year that is similar to year zero. For example, this is 420 A.D. Then we will use this year to display the month of the zero year, and when displaying it, indicate that it is -0001. And then you can move away from the concept of a zero year, because there were of the year.

with input as (
    select to_date(:dt, 'mm.syyyy') dt
    from dual
)
select case
        when extract (year from dt) > 0 then 
          to_char(level + dt - 1, 'fm dd ') || 
          to_char(level + dt - 1, 'MONTH ', 'nls_date_language=american') ||
          to_char(level + dt - 1, 'syyyy ', 'nls_date_language=american') ||
          to_char(level + dt - 1, 'day', 'nls_date_language=american')
        when extract (year from dt) = -1 then
          to_char(level + to_date(regexp_replace(to_char(dt, 'mm.syyyy'), '-0001', '0420'), 'mm.syyyy') - 1, 'fm dd ') ||
          to_char(level + to_date(regexp_replace(to_char(dt, 'mm.syyyy'), '-0001', '0420'), 'mm.syyyy') - 1, 'MONTH ', 'nls_date_language=american') ||
          to_char(level + dt - 1, 'syyyy ', 'nls_date_language=american') ||
          to_char(level + to_date(regexp_replace(to_char(dt,'mm.syyyy'), '-0001', '0420'),'mm.syyyy') - 1, 'day', 'nls_date_language=american')
        else
          to_char(level + add_months(dt, 12) - 1, 'fm dd ') ||
          to_char(level + add_months(dt, 12) - 1, 'MONTH ', 'nls_date_language=american') ||
          to_char(level + dt - 1, 'syyyy ', 'nls_date_language=american') ||
          to_char(level + add_months(dt, 12) - 1, 'day', 'nls_date_language=american')
        end calendar
from dual
     join input on 1 = 1
connect by level + dt - 1 <= last_day(dt);

The resulting request helps to achieve the desired result. This is how the problem, in which I was absolutely sure, was solved incorrectly by me on the exam.