Create date from year, month and day

Question

Does Oracle have a builtin function to create a date from its individual components (year, month and day) that just returns null on missing data?

I'm aware of TO_DATE() but I need to compose a string first and neither the || operator nor the CONCAT() function make it easy to handle missing data:

-- my_year NUMBER(4,0) NULL
SELECT TO_DATE(my_year || '-01-01', 'YYYY-MM-DD') AS my_date
FROM my_table;

Whenever my_year is NULL we end up with TO_DATE('-01-01', 'YYYY-MM-DD') and:

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

Answer 1

For your example, you can use 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)

Unfortunately, Oracle does not have a method of doing the conversion, if possible, and otherwise returning NULL. SQL Server recently introduced try_convert() for this purpose.

One option is to write your own function with an exception handler for the failed conversion. The exception handler would simply return NULL for a bad format.