使用Oracle SQL的当前出生日期

Question

正在尝试获取当前年份的出生日期。 例如，如果 DOB=19800925，则当前年份的出生日期为 20180925 (注意：我们不应该因为闰年 DOB 而替换年份(例如：20000229((

with tmp_dob as 
(
select '19900101' Birthday from dual union all
select '19901231' Birthday from dual union all
select '20040229' Birthday from dual union all
select '20041231' Birthday from dual union all
select '20171231' Birthday from dual union all
select '20051231' Birthday from dual 
)
select Birthday,add_months(to_date(Birthday,'YYYYMMDD'),(trunc(months_between(sysdate ,to_date(Birthday,'YYYYMMDD'))/12)) * 12) current_year_dob 
from tmp_dob;

Answer 1

这个逻辑应该有效：

with tmp_dob as (
select to_date('19900101', 'YYYYMMDD') as Birthday from dual union all
select to_date('19901231', 'YYYYMMDD') Birthday from dual union all
select to_date('20040229', 'YYYYMMDD') Birthday from dual union all
select to_date('20041231', 'YYYYMMDD') Birthday from dual union all
select to_date('20171231', 'YYYYMMDD') Birthday from dual union all
select to_date('20051231', 'YYYYMMDD') Birthday from dual 
)
select Birthday,
add_months(birthday, 12 * (extract(year from sysdate) - extract(year from birthday)))
from tmp_dob;

Answer 2

添加月份方法有效，但闰日生日会得到 2/28。如果您希望为 03/01 或可配置，请使用案例来检测条件并覆盖：

with tmp_dob as (
select to_date('19900101', 'YYYYMMDD') as Birthday from dual union all
select to_date('19901231', 'YYYYMMDD') Birthday from dual union all
select to_date('20040229', 'YYYYMMDD') Birthday from dual union all
select to_date('20041231', 'YYYYMMDD') Birthday from dual union all
select to_date('20171231', 'YYYYMMDD') Birthday from dual union all
select to_date('20051231', 'YYYYMMDD') Birthday from dual 
)
select Birthday,
case 
when 
to_date(extract(year from sysdate) || '1231', 'YYYYMMDD') -
to_date(extract(year from sysdate) || '0101', 'YYYYMMDD') < 365
and to_char(birthday, 'MMDD') = '0229' then
to_date(extract(year from sysdate) || '0301', 'YYYYMMDD')
else  
add_months(birthday, 12 * (extract(year from sysdate) - extract(year from birthday)))
end as bd_this_year
from tmp_dob;

结果：

BIRTHDAY    BD_THIS_YEAR
1/1/1990    1/1/2018
12/31/1990  12/31/2018
2/29/2004   3/1/2018
12/31/2004  12/31/2018
12/31/2017  12/31/2018
12/31/2005  12/31/2018

考虑创建一个函数来做同样的事情 - 你的查询会更容易阅读。

Answer 3

我想过使用字符串的方法，因为您使用的生日也在字符串上。 我使用 BigQuery 语法，但每个 SQL 中都应该有类似的函数。

with tmp_dob as
(
select '19900101' Birthday from dual union all
select '19901231' Birthday from dual union all
select '20040229' Birthday from dual union all
select '20041231' Birthday from dual union all
select '20171231' Birthday from dual union all
select '20051231' Birthday from dual 
)
select Birthday,
CASE WHEN MOD(CAST(SUBSTR(Birthday,1,4) AS INT64), 4) = 0 AND SUBSTR(Birthday,5,8) = '0229'
THEN CONCAT(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS STRING), '0228')
ELSE CONCAT(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS STRING), SUBSTR(Birthday,5,8)) END AS bd
from tmp_dob;