SantoshN
SantoshN

Reputation: 3

current year's date of birth using oracle sql

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

Answers (3)

dandarc
dandarc

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

Ary Jazz
Ary Jazz

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

Gordon Linoff
Gordon Linoff

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

Related Questions