Birthday Query in VFP

It's a common request to get a list of people whose birthday (or an anniversary date) falls into specified date range w/o regard to the year.

In the query below a birthday date is converted to the year of the range start and end dates, than both results are compared to the date range itself. The GOMONTH() function gracefully converts February 29 to February 28 for a none leap years. The query correctly handles empty and NULL dates as well.

The query's based on Birthday Query in MS SQL Servers

This is sample code. Add error handling and adjust to your requirements as necessary.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

ldStartDate = {^2009-02-22} 
ldEndDate   = {^2009-02-28}

*ldStartDate = {^2008-02-22}
*ldEndDate   = {^2008-02-28}

SELECT * ;
	FROM People ;			
	WHERE GOMONTH(dob, (YEAR(ldStartDate) - YEAR(dob) ) * 12) BETWEEN ldStartDate AND ldEndDate ;
	   OR GOMONTH(dob, (YEAR(ldEndDate) - YEAR(dob) ) * 12) BETWEEN ldStartDate AND ldEndDate 

Sample data for the Birthday query
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE CURSOR People (PK I autoinc, FullName C(30), dob D NULL)

INSERT INTO People (FullName, dob) VALUES ("John Smith", {^1965-02-28})
INSERT INTO People (FullName, dob) VALUES ("Alex Black", {^1960-02-29})
INSERT INTO People (FullName, dob) VALUES ("Bill Doors", {^1968-02-27})
INSERT INTO People (FullName, dob) VALUES ("Sam Jobless", {^1958-03-01})
INSERT INTO People (FullName, dob) VALUES ("Nancy Davolio", {^1968-12-08})
INSERT INTO People (FullName, dob) VALUES ("Andrew Fuller", {^1952-02-19})
INSERT INTO People (FullName, dob) VALUES ("Janet Leverling", {^1963-08-30})
INSERT INTO People (FullName, dob) VALUES ("Margaret Peacock", {^1958-09-19})
INSERT INTO People (FullName, dob) VALUES ("Steven Buchanan", {^1955-03-04})
INSERT INTO People (FullName, dob) VALUES ("Michael Suyama", {^1963-07-02})
INSERT INTO People (FullName, dob) VALUES ("Robert King", {^1960-05-29})
INSERT INTO People (FullName, dob) VALUES ("Laura Callahan", {^1958-01-09})
INSERT INTO People (FullName, dob) VALUES ("Anne Dodsworth", {^1969-07-02})
INSERT INTO People (FullName, dob) VALUES ("Jack Lighthouse", NULL)
INSERT INTO People (FullName, dob) VALUES ("Cathy White", {})

Comments