terça-feira, 10 de julho de 2007

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


Um comentário:

HERMES SOARES disse...

Interessante, mais gostaria de saber qual e o numero da semana dentro de um mes, tipo se essa e a primeira semana do mes, segunda etc!!!

Neobux