根据年，月和日创建日期 [英] Create date from year, month and day

问题描述

Oracle是否具有内置功能，可以根据其各个组成部分(年，月和日)创建仅对丢失的数据返回false的日期?

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

我知道TO_DATE()，但是我需要首先编写一个字符串，并且||运算符和CONCAT()函数都无法轻松处理丢失的数据:

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;

只要my_year是NULL，我们都会以TO_DATE('-01-01', 'YYYY-MM-DD')结尾:

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

推荐答案

对于您的示例，您可以使用case:

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)

不幸的是，Oracle没有一种方法(如果可能)进行转换，否则将返回NULL. SQL Server为此最近引入了try_convert().

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.

一个选择是为失败的转换编写一个带有异常处理程序的函数.异常处理程序只会以错误的格式返回NULL.

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.

这篇关于根据年，月和日创建日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！