Birthday query

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Sid
Fan

Birthday query

Hi All,

I want in my query to find customers whose birthday falls in next 90 days.

I am using below query:

 

SELECT

CUST_NUM,CUST_NAME, 

CUST_DOB,
TRIM(CAST( ( CURRENT_DATE-CUST_DOB YEAR(4)) AS INTEGER)) AS CUST_AGE
FROM TBL
WHERE
CAST(TRIM(EXTRACT(YEAR FROM CURRENT_DATE))||'-'||SUBSTR(TRIM(CAST(CUST_DOB AS DATE FORMAT 'YYYY-MM-DD')),6,5) AS DATE FORMAT 'YYYY-MM-DD') >= CURRENT_DATE
AND CAST(TRIM(EXTRACT(YEAR FROM CURRENT_DATE))||'-'||SUBSTR(TRIM(CAST(CUST_DOB AS DATE FORMAT 'YYYY-MM-DD')),6,5) AS DATE FORMAT 'YYYY-MM-DD') - CURRENT_DATE <=90;

 

This query does not support leap year birthday cases when customer birthday falls on 29th Feb.

Is there any way to handle this particular case?

1 REPLY
Junior Contributor

Re: Birthday query

You can use one of the following calculations to get the actual age (Interval arithmetic will not return a result a human being expects):

 

/***
age in years, two variations regarding leap years:
is the duration between 2012-02-29 and 2013-02-28 a full year?
***/

/***
age in years,
duration between 2012-02-29 and 2013-02-28 is a full year:

SELECT age(DATE '2013-02-28', DATE '2012-02-29');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

age(2013-02-28,2012-02-29)
--------------------------
                         1
***/
REPLACE FUNCTION age(d1 DATE, d2 DATE)
RETURNS INT
SPECIFIC age_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
   MONTHS_BETWEEN(d1,d2)/12 (INT)
;

/***
age in years,
duration between 2012-02-29 and 2013-02-28 is not a full year:

SELECT age2(DATE '2013-02-28', DATE '2012-02-29');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

age2(2013-02-28,2012-02-29)
--------------------------
                         0
***/
REPLACE FUNCTION age2(d1 DATE, d2 DATE)
RETURNS INT
SPECIFIC age2_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
  ((d1 (INT)) - (d2 (INT))) / 10000
;

 

And then simply calculate a person's next birthday and check if it's within the specified range:

 

SELECT ...
   age2(Current_Date,CUST_DOB)+1 AS CUST_AGE,
   Add_Months(CUST_DOB, 12 * CUST_AGE) AS next_birthday
FROM TBL
WHERE next_birthday BETWEEN Current_Date AND Current_Date + 90;