Создать дату из года, месяца и дня

В вашем примере вы можете использовать case:

select (case when my_year is not null and my_year <> 0 and
                  my_year between -4713 and 9999
             then TO_DATE(my_year || '-01-01', 'YYYY-MM-DD')
        end)

К сожалению, у Oracle нет способа выполнить преобразование, если это возможно, и в противном случае вернуть NULL. SQL Server недавно представил для этой цели try_convert().

Один из вариантов — написать собственную функцию с обработчиком исключений для неудачного преобразования. Обработчик исключений просто вернул бы NULL для неправильного формата.

Вы не можете использовать нулевой год с to_date('0000-01-01', 'YYYY-MM-DD'), но, как ни странно, можете с литералом даты date '0000-01-01'. Само по себе это становится годом -1, но вы можете использовать его для расчетов; и вы также можете добавить интервал, который может быть основан на вашем числовом значении, например:

SELECT DATE '0000-01-01' + NUMTOYMINTERVAL(my_year, 'YEAR') AS my_date
FROM my_table;

Функция numtoyminterval возвращает null, если аргумент равен null, и добавление этого к фиксированной дате также дает вам ноль:

alter session set nls_date_format = 'SYYYY-MM-DD';

select date '0000-01-01' + numtoyminterval(null, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(NULL,'YEAR')
---------------------------------------------


select date '0000-01-01' + numtoyminterval(2015, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(2015,'YEAR')
---------------------------------------------
 2015-01-01                                  

select date '0000-01-01' + numtoyminterval(9999, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(9999,'YEAR')
---------------------------------------------
 9999-01-01                                  

select date '0000-01-01' + numtoyminterval(-4712, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(-4712,'YEAR')
----------------------------------------------
-4712-01-01                                   

Это не надежно; все равно будет ошибка, если вы попытаетесь перейти до -4713:

select date '0000-01-01' + numtoyminterval(-4713, 'YEAR') from dual;

SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
...

Хотя вы можете избежать этого с помощью контрольного ограничения для столбца. И из-за молчаливого перевода года 0 в -1 вы получите тот же ответ, если ваше значение my_year равно 0 или -1:

select date '0000-01-01' + numtoyminterval(0, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(0,'YEAR')
------------------------------------------
-0001-01-01                               

select date '0000-01-01' + numtoyminterval(-1, 'YEAR') from dual;

DATE'0000-01-01'+NUMTOYMINTERVAL(-1,'YEAR')
-------------------------------------------
-0001-01-01                                

Кейс-подход Гордона Линоффа более надежен, но он может сработать, если вы действительно имеете дело только с «нормальными» положительными годами. А если нет, то это очень интересно...

В конце концов я составил определяемую пользователем функцию для инкапсуляции логики. Он возвращает дату из своих отдельных компонентов или NULL, если дата недействительна:

CREATE OR REPLACE FUNCTION TRY_TO_DATE (
    V_YEAR IN NUMBER,
    V_MONTH IN NUMBER,
    V_DAY IN NUMBER
) RETURN DATE DETERMINISTIC IS
BEGIN
    RETURN TO_DATE(LPAD(V_YEAR, 4, '0') || LPAD(V_MONTH, 2, '0') || LPAD(V_DAY, 2, '0'), 'YYYY-MM-DD');
    EXCEPTION
        WHEN OTHERS THEN RETURN NULL;
END TRY_TO_DATE;
/

Скрипка

where my_year > 0

Это просто вернет строки, в которых можно создать дату. Игнорирование строк с нулевым значением или со значением 0. Если у вас есть некоторые значения, не входящие в диапазон -4713 и +9999, вы, конечно, должны также исключить те, которые указаны в предложении where.