Date functions

Now that IB6 has built-in EXTRACT function, some older problems can be solved more easily.

Day of Week

In IB6 there is a new built-in function EXTRACT(). This call 
  EXTRACT(WEEKDAY FROM D)
will return 0=Sunday, 1=Monday, ... 6=Saturday.

On the other hand, International Standard ISO 8601 specifies that week begins on Monday and that it is day 1. To get Day of Week by ISO standard, use: 

  EXTRACT(WEEKDAY FROM D-1)+1
which will return 1=Monday, 2=Tuesday, ... 7=Sunday.

Example: 
SELECT
  D,
  EXTRACT( WEEKDAY FROM D)          AS AMERICAN,
  EXTRACT( WEEKDAY FROM D - 1) + 1  AS ISO8601
FROM T;

          D AMERICAN     ISO8601
=========== ======== ===========
29-NOV-2001        4           4
30-NOV-2001        5           5
 1-DEC-2001        6           6
 2-DEC-2001        0           7    <<<<< Sunday
 3-DEC-2001        1           1
 4-DEC-2001        2           2
 5-DEC-2001        3           3

First/last day of month

First day of month: 
    D - EXTRACT(DAY FROM D) + 1;
Last day of month: 
    LDM = D   - EXTRACT(DAY FROM D) + 32;
    LDM = LDM - EXTRACT(DAY FROM LDM);
or as single expression 
    D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)
First day of next month: 
    FDNM = D    - EXTRACT(DAY FROM D) + 32;
    FDNM = FDNM - EXTRACT(DAY FROM FDNM) + 1;
or as single expression 
    D - EXTRACT(DAY FROM D) + 33 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)

Example: if the day is D='2002-3-22', 
first day of month will be '2002-3-1'
last  day of month will be '2002-3-31'
first day of next month will be '2002-4-1'

Number of days in month

Number of days in month procedure: 
CREATE PROCEDURE MonthLength (D DATE) RETURNS (ML INTEGER) AS
DECLARE VARIABLE TMP DATE;
BEGIN
    TMP = D - EXTRACT(DAY FROM D) + 32;
    ML  = EXTRACT(DAY FROM (TMP - EXTRACT(DAY FROM TMP)));
END
or as single expression 
    EXTRACT(DAY FROM (D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32)))

Week of year

ISO 8601 standard (for Date and Time Notation) specifies that week begins with a monday, and that the first week of a year is the one which includes the first thursday. (In other words, week belongs to the year where lies its bigger part.) This is the basic formula: 
    (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7
It can return value between 0 and 53. Sometimes it can be sufficient, however if we want to conform ISO standard, we need to make some adjustments in case the result is zero or 53.

If the result was zero, it means that in fact the week belongs to previous year. The last week of previous year can be either 52 or 53; to find out which one, we just repeat the same formula (with the last day of previous year as parameter).

If the result was 53, we must check whether it is really 53rd week, or whether it is the first week of the next year. 53rd week is valid only if it contains thursday (i.e. 31.12. is either thursday or friday).

These adjustments can easily be avoided if we compute the formula for the thursday of the week we are interested in. 

CREATE PROCEDURE YearWeek (D DATE)
  RETURNS (WEEK_NO VARCHAR(8)) AS
DECLARE VARIABLE W INTEGER;  /* week number */
DECLARE VARIABLE Y INTEGER;  /* year the week belongs to */
BEGIN
  D = D - EXTRACT(WEEKDAY FROM D-1) + 3;  /* move to thursday */

  W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7e0;
  Y = EXTRACT(YEAR FROM D);

  /* This is just formatting; you may prefer to make W and Y return parameters instead. */
  IF (W<10) THEN
    WEEK_NO = '0';
  ELSE
    WEEK_NO = '';
  WEEK_NO = Y || '/' || WEEK_NO || W;

  SUSPEND;
END


Original code worked with dialect-1 only. Here it is with fix to work with all dialects: 
CREATE PROCEDURE YearWeek_old (D DATE)
  RETURNS (WEEK_NO VARCHAR(8)) AS
DECLARE VARIABLE W INTEGER;  /* week number */
DECLARE VARIABLE Y INTEGER;  /* year the week belongs to */
BEGIN
  W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7e0;
  Y = EXTRACT(YEAR FROM D);

  IF (W=0) THEN BEGIN
    Y = Y - 1;
    D = D - EXTRACT(YEARDAY FROM D) - 1;  /* last day of previous year; D is used as temporary variable here */
    W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7e0;
    END
  ELSE
  IF (W=53 AND 4>EXTRACT(WEEKDAY FROM (D - EXTRACT(DAY FROM D) + 31))) THEN BEGIN
    Y = Y + 1;
    W = 1;
    END

  /* This is just formatting; you may prefer to make W and Y return parameters instead. */
  IF (W<10) THEN
    WEEK_NO = '0';
  ELSE
    WEEK_NO = '';
  WEEK_NO = Y || '/' || WEEK_NO || W;

  SUSPEND;
END

Is Leap Year ?

To find out whether given date belongs to leap year, we can look e.g. at 59th day in year;
it is either February 29 for leap year, or it is March 1 for normal year.

Here is example of stored procedure: 

CREATE PROCEDURE Is_LeapYear (D DATE) RETURNS (LY INTEGER) AS
BEGIN
  IF ( 2 = EXTRACT(MONTH FROM (D - EXTRACT(YEARDAY FROM D) + 59)) ) THEN
    LY = 1;  /* leap year */
  ELSE
    LY = 0;  /* normal year */
END

Another procedure (with Year as input parameter): 

CREATE PROCEDURE Is_LeapYear (Y INTEGER) RETURNS (LY INTEGER) AS
BEGIN
  IF ( 60 = EXTRACT(YEARDAY FROM CAST(Y || '-3-1' AS TIMESTAMP)) ) THEN
    LY = 1;  /* leap year */
  ELSE
    LY = 0;  /* normal year */
END
Because IB5 does not support EXTRACT function, we can just test whether February 29th exists: 
CREATE PROCEDURE Is_LeapYear (Y INTEGER) RETURNS (LY INTEGER) AS
DECLARE VARIABLE D DATE;
BEGIN
  LY = 1;
  D  = CAST('29-FEB-' || Y AS DATE);
  WHEN ANY DO LY = 0;
END
Well known expression for testing leap year is this 
Is_LeapYear :=
   ( ((Year MOD 4) = 0) AND ((Year MOD 100) <> 0) )
   OR ((Year DIV 400) = 0);
but IB/FB does not directly support MOD operator; it can be implemented either with UDF, or by proper implementing of expression
x MOD y = x - (x DIV y) * y
(which is not as easy as it seems to be at first sight because of different rules for dividing in Dialect-1 and Dialect-3).

Copyright © 2002,2005 Ivan Prenosil