Oracle upcoming birthdate todays year

Question

I want to write a query that tells me when is a person's particular birthday this year. for leap years just go with the next month. I was planning to achieve this by calculating the difference between today and their date of birth and adding it to their date of birth. If a person is born in first of November of 1999 I want to calculate when is their birthday for this year. The expected output is first of November of this year. This are my attempts to achieve this:

I tried by counting how many months between the two dates and adding it to the date of birth.

select  ADD_MONTHS(to_date('01-NOV-1999'), ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), to_date('01-NOV-1999'))))  from dual;

This output the following: 01-OCT-19

I tried calculating how many days in between the birth date and today and adding that to their date of birth. However, this doesn't seem to work correctly:

select  to_date('01-NOV-1999') + (TRUNC(SYSDATE) - to_date('01-NOV-1999'))  from dual;

OUTPUT: 02-OCT-19

What I hope to get is 01-Nov-2019 (the system date I posted this is 02-OCT-19) both seem to be a month behind my expected output. If I try to run the same query with OCT instead of NOV in the first query, it works correctly.

I am using Oracle Database 11g

Koen Lostrie Koen Lostrie · Accepted Answer · 2019-10-02T18:17:36

A birthday is not a formula counting from the date of birth. It is a fixed day of the year, except for the leap years. Using that logic you could calculate the birthdays in this year with the following query:

with date_of_birth as
(SELECT TO_DATE('01-NOV-1971','DD-MON-YYYY') as dt FROM DUAL
UNION
SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY')FROM DUAL
)
SELECT 
  dt as date_of_birth
 ,CASE 
    WHEN TO_CHAR(dt,'DD-MON') = '29-FEB' THEN
      TO_DATE('01-MAR-'||EXTRACT(year FROM sysdate),'DD-MON-YYYY')
    ELSE 
      TO_DATE(TO_CHAR(dt,'DD-MON-')||EXTRACT(year FROM sysdate),'DD-MON-YYYY')
  END AS this_year_birthday
  FROM date_of_birth;

and that will return

DATE_OF_BIR THIS_YEAR_B
----------- -----------
01-NOV-1971 01-NOV-2019
29-FEB-2004 01-MAR-2019

-- edited. As Jeffrey points out the above query returns the wrong result if run in a leap year. Here is an updated version that allows the user to run it in another than the current year. Will return the same results as Jeffrey Kemps' answer but takes it from another angle.

with 
FUNCTION is_valid_date (date_str_i VARCHAR2, format_i VARCHAR2) RETURN VARCHAR2
/* check if date is valid */
AS
  l_dummy_dt DATE;
  date_not_valid_for_m EXCEPTION;
  PRAGMA EXCEPTION_INIT(date_not_valid_for_m, -01839);  
BEGIN
  SELECT TO_DATE(date_str_i,format_i) INTO l_dummy_dt FROM DUAL;
  RETURN 'Y';
EXCEPTION WHEN date_not_valid_for_m THEN
  RETURN 'N';
END; 
date_of_birth as
(SELECT TO_DATE('01-NOV-1971','DD-MON-YYYY') as dt FROM DUAL
UNION
SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY')FROM DUAL
),
year_to_check as
(SELECT '2016' as yr FROM DUAL)
SELECT 
  dt as date_of_birth
 ,CASE 
    WHEN TO_CHAR(dt,'DD-MON') = '29-FEB' THEN
      CASE 
        WHEN is_valid_date(date_str_i => '29-FEB-'||yr,format_i => 'DD-MON-YYYY') = 'N' 
          THEN TO_DATE('01-MAR-'||yr,'DD-MON-YYYY')
        ELSE
          TO_DATE(TO_CHAR(dt,'DD-MON-')||yr,'DD-MON-YYYY')
        END 
    ELSE 
      TO_DATE(TO_CHAR(dt,'DD-MON-')||yr,'DD-MON-YYYY')
  END AS this_year_birthday
  FROM date_of_birth, year_to_check;
/

Oracle upcoming birthdate todays year

2 Answers