Time to Time Functions Help Informix

Question

How to use the add date or date functions? I have a scenario where I need to find people whose birthdays are either today or after n days. How can I achieve this 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 can 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 create a date using MDY with MONTH and DAY from bdate and YEAR from TODAY . You will then see if it is between the dates you want to match.

Documentation for MDY :

The MDY function takes three integer expressions as arguments, which represent the month, day, and year and return a value of type DATE.

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