Reputation: 3
Trying to get birth day date in the current year. For an example if DOB=19800925, birth day in the current year is 20180925 (note: we should not just replace the year because of the leap year DOB (ex: 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;
Upvotes: 0
Views: 1234
Reputation: 678
Add months approach works, but you'll get 2/28 for the leap-day birthdays. If you want that to be 03/01, or to be configurable, use a case to detect the condition and override:
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;
Results:
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
Consider making a function to do the same thing - your query will be easier to read.
Upvotes: 1
Reputation: 1656
I thought about using an approach of strings since the birthdays you are using are on strings too. I use the BigQuery Syntax but there should be similar functions in every 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;
Upvotes: -1
Reputation: 1269803
This logic should work:
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;
Upvotes: 3