Date time functions help informix

Question

How can I use the date add or date diff functions I have a scenario where I need find people whose birthdays are either today or after n number of days. How can I achieve it in informix.

SELECT mbr_code, fname, lname
INTO rsMbrCode, rsFName, rsLName
FROM asamembr
WHERE cust_code = membershipnumber 
    AND ((day(bdate) - day(CURRENT)) <= rsTest 
    AND MONTH(bdate) = month(CURRENT))

RETURN rsMbrCode, rsFName, rsLName WITH RESUME;

Answer 1

You could do something like this:

SELECT mbr_code,fname,lname
INTO rsMbrCode,rsFName,rsLName
FROM asamembr
WHERE cust_code = membershipnumber 
    AND MDY(month(bdate),day(bdate),year(today)) 
    BETWEEN TODAY AND TODAY + <NUMBEROFDAYS> UNITS DAY;

You construct a date with Using MDY with the MONTH and DAY from bdate and YEAR from TODAY. Then you see if it is between the dates you want to match.

Documentation for MDY:

The MDY function takes as its arguments three integer expressions that represent the month, day, and year, and returns a type DATE value.

• The first argument represents the number of the month (1 to 12).
• The second argument represents the number of the day of the month (1 to 28, 29, 30, or 31, as appropriate for the month)
• The third expression represents the 4-digit year. You cannot use a 2-digit abbreviation.