DotNetFirebird.org DotNetFirebird
Using Firebird SQL in .NET.
Sunday, January 09, 2005

Date Functions

Cut and paste date functions. See Ivan Prenosil's site.

Day of week (American format: week starting on Sunday, Sunday is 0):

EXTRACT(WEEKDAY FROM D)

Day of week (ISO 8601 format: week starting on Monday, Monday is 1):

EXTRACT(WEEKDAY FROM D-1)+1

First day of a month:

D - EXTRACT(DAY FROM D) + 1;

Last day of a month:

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

Number of days in a month:

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

Week of a year (ISO 8601) stored procedure:

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

Comments:

Blog comments are closed.



Previous

Archives