Date Functions

Algumas funções interessantes de Data e Hora no Firebird.
Leia mais no site: Ivan Prenosil's site

Dia da semana - Day of week (Formato americano: Semanas iniciam no Domingo, Domingo é 0):

EXTRACT(WEEKDAY FROM D)

Dia da semana - Day of week (Formato ISO 8601: a semana inicia na segunda, Segunda é 1):

EXTRACT(WEEKDAY FROM D-1)+1

Primeiro dia do mês:

D - EXTRACT(DAY FROM D) + 1;

Último dia do mês:

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

Número de dias no mês:

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

Semana do ano (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

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