Нулевой год в БД Oracle

На третьем курсе в СПБПУ Петра Великого у меня был экзамен по SQL в БД Oracle. Экзамен состоял из трех задач. Этот экзамен был одним из самых сложных за все четыре года обучения в университете. Дается три задачи на несколько часов. Если решил все три задачи, то получаешь оценку 5, один недочет (например, лишний пробел) — 4, одна задача решена неверна — 3, два недочета — 3.

Сегодня я хочу рассказать об одной из задач, которая была в этом экзамене.

Используя обращение только к таблице DUAL, построить SQL-запрос, возвращающий один столбец, содержащий календарь на заданный месяц заданного года:

• номер дня в месяце (цифрами),

• полное название месяца по-английски заглавными буквами (в верхнем регистре),

• год (четыре цифры),

• полное название дня недели по-английски строчными буквами (в нижнем регистре).

Каждое «подполе» должно быть отделено от следующего одним пробелом. В результате не должно быть начальных и хвостовых пробелов. Количество возвращаемых строк должно точно соответствовать количеству дней в текущем месяце. Строки должны быть упорядочены по номерам дней в месяце по возрастанию.

Календарь должен создаваться для любых допустимых значений дат Oracle. Задачу решить без использования разделов Model и рекурсивного With.

Пример вывода результата:

1 MAY 2020 friday

2 MAY 2020 saturday

Задача показалась мне самой простой. Приведу запрос, с помощью которого я решил данную задачу.

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);

На первый взгляд все выглядит хорошо, но рассмотрим один интересный момент. Допустимый диапазон для года — это целое число от -4172 до 9999, кроме 0. Возьмем декабрь 1 года до н.э. и январь 1 года н.э. В результате мы увидим, что после среды идет суббота, а значит задача решена неверно.

В григорианском и юлианском календарях отсутствует нулевой год. Также и в Oracle нет нулевого года, а -0001 год Oracle соответствует 2 году до н.э. по традиционной хронологии.

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

Тогда даты, которые относятся к нашей эре продолжаем создавать, как это делалось в первом запросе. -0002 год Oracle «сдвинем» вправо, то есть он станет 2 годом до н.э. по традиционной хронологии, -0003 Oracle, станет 3 до н.э. и т.д. Но мы не можем также сдвинуть -0001 год Oracle, добавляя дни/месяцы и др. Однако можно найти год, который будет похож на нулевой год. Например, это 420 год н.э. Тогда будем использовать этот год для вывода месяца нулевого года, а при выводе указывать, что это -0001. И тогда можно отойти от понятия нулевого года, ведь остались года.

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);

Полученный запрос помогает добиться необходимого результата. Вот так задача, в которой я был абсолютно уверен, была решена неверно мной на экзамене.