Date/Time functions

Source: Ivan Prenosil's InterBase site

Week-of-year etc.

• Day of Week
• First/last day of month
• Number of days in month
• Week of year
• Is Leap Year ?

Day of Week

  EXTRACT(WEEKDAY FROM D)

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

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

    D - EXTRACT(DAY FROM D) + 1; 

    LDM = D   - EXTRACT(DAY FROM D) + 32;
    LDM = LDM - EXTRACT(DAY FROM LDM); 

    D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32) 

    FDNM = D    - EXTRACT(DAY FROM D) + 32;
    FDNM = FDNM - EXTRACT(DAY FROM FDNM) + 1; 

    D - EXTRACT(DAY FROM D) + 33 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32) 

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

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 

    EXTRACT(DAY FROM (D - EXTRACT(DAY FROM D) + 32 - EXTRACT(DAY FROM D - EXTRACT(DAY FROM D) + 32))) 

Week of year

    (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7

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 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).

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
  W = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7;
  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) / 7;
    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 ?

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 

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 

Is_LeapYear :=
   ( ((Year MOD 4) = 0) AND ((Year MOD 100) <> 0) )
   OR ((Year DIV 400) = 0);