Date functions

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

* Day of Week
* First/last day of month
* Number of days in month
* Week of year Updated New code is simpler, and works with all dialects.
* Is Leap Year ?

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

in Ivan Prenosil; http://www.volny.cz/iprenosil/interbase/ip_ib_code_timestamp.htm

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: