Did you ever forget a birthday or anniversary?

Let's imagine you have a table with members -- clients or other people -- and that for each person you have an associated birth date.

It would be useful to have a birthday calendar for these people. Then you could send an electronic postcard on their birthdays. Imagine how surprised your family and friends will be when you don't forget their birthdays anymore!

Here's how to set up such a calendar with InterBase. Your family will be so grateful.

Extracting the day and month from a date

One problem with building a birthday calendar is that we must convert birth dates into days and month numbers so we can sort them. InterBase includes a number of functions to deal with column data, and if they're insufficient you can write your own -- a user-defined function, or UDF. InterBase 6 comes with UDFs that perform some handy tricks. One of these is the substr function that I use in this article. For a complete list of all functions, look in your InterBase install directory, in the subdirectory examplesUdf, and view the file ib_udf.sql.

Here is a SQL script that you could execute with ISQL. The script contains an example of a birthday calendar.

CREATE DATABASE 'c:birthdates.gdb' USER 'SYSDBA' PASSWORD 'masterkey';

/*****************************************
 *	s u b s t r
 *	substr(s,m,n) returns the substring
 *	of s which starts at position m and
 *	ending at position n.
 *****************************************/
DECLARE EXTERNAL FUNCTION substr
	CSTRING(80), SMALLINT, SMALLINT
	RETURNS CSTRING(80) FREE_IT
	ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

CREATE TABLE MEMBER (
  MEMBERNAME VARCHAR(30) NOT NULL,
  BIRTHDATE DATE,
  PRIMARY KEY (MEMBERNAME));

CREATE VIEW BIRTHDATECALENDAR (DAY, MONTH, MEMBERNAME)
  AS SELECT
    CAST(substr(CAST(BIRTHDATE AS CHAR(10)),9,10) AS INTEGER),
    CAST(substr(CAST(BIRTHDATE AS CHAR(10)),6, 7) AS INTEGER),
    MEMBERNAME FROM MEMBER;

COMMIT;

INSERT INTO MEMBER
VALUES ('Marco Hemmes', '22-JAN-1972');
INSERT INTO MEMBER
VALUES ('Rebecca Hemmes', '4-MAY-1997');
INSERT INTO MEMBER
VALUES ('Ruben Hemmes', '12-NOV-1999');
INSERT INTO MEMBER
VALUES ('Koningin Beatrix', '31-JAN-1938');
INSERT INTO MEMBEr
VALUES ('Prins Claus', '6-SEP-1926');
INSERT INTO MEMBER
VALUES ('Prins Bernhard', '29-JUN-1911');
INSERT INTO MEMBER
VALUES ('Prinses Juliana', '30-APR-1909');
INSERT INTO MEMBER
VALUES ('Prins Willem-Alexander', '27-APR-1967');
INSERT INTO MEMBER
VALUES ('Maxima Zorreguieta', '17-MAY-1971');
INSERT INTO MEMBER
VALUES ('Sinterklaas', null);

COMMIT;

SELECT * FROM BIRTHDATECALENDAR ORDER BY MONTH, DAY;

The output of this script, if executed with ISQL, looks like this:

         DAY        MONTH MEMBERNAME
============ ============ ==============================

          22            1 Marco Hemmes
          31            1 Koningin Beatrix
          27            4 Prins Willem-Alexander
          30            4 Prinses Juliana
           4            5 Rebecca Hemmes
          17            5 Maxima Zorreguieta
          29            6 Prins Bernhard
           6            9 Prins Claus
          12           11 Ruben Hemmes
      <null>       <null> Sinterklaas

You can reproduce this example by saving the script as "birthdates.sql." Now execute isql -i birthdates.sql and you should get the same output. You may have to change the password in the first line of the script, and under Linux you have to change the location of the database in the first line of the script.

Using the substr UDF

With DECLARE EXTERNAL FUNCTION you can make a link to a function in an external library -- in this case the sample ib_udf library that is located in the Udf subdirectory of your InterBase installation.

The MEMBER table is filled with my family data and data of some other Dutch people. The view BIRTHDATECALENDAR converts the birth date to a string with a CAST, then the daynumber or monthnumber is extracted from the string with the UDF function substr. The daynumber and monthnumber are converted to integers with another CAST. The view cannot contain a sorting order, but the SELECT sorts the view by month and day.

Look for more information about UDFs in the InterBase manual.

Marco Hemmes
Bergler Nederland B.V.
marco.nldelphi.net