Процедуры для работы с датой и временем

Примеры.

Следующая инструкция удаляет процедуру:

DROP PROCEDURE GET_EMP_PROJ;

EXTRACT() function

The EXTRACT() function extracts date and time information from databases.

EXTRACT() has the following syntax:

EXTRACT (part FROM value)

The value passed to the EXTRACT() expression must be a DATE, TIME, or TIMESTAMP datatype. Extracting a part that doesn’t exist in a datatype results in an error. For example, the following expression would fail:

EXTRACT (YEAR FROM aTime).

CAST() function

You can use the CAST() function in SELECT statements to translate between date/time datatypes and various character-based datatypes. It is not possible to cast a date/time datatype to or from BLOB, SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC, or DECIMAL datatypes.

Note that there are some differences from behavior in past versions of InterBase:

Casting DATE to string results in YYYY-MM-DD where” M”M is a two-digit month. If the result does not fit in the string variable a string truncation exception is raised. In earlier versions, this case results in DD-Mon- YYYY HH:mm:SS.hundreds where “Mon” was a 3-letter English month abbreviation. Inability to fit in the string variable resulted in a silent truncation.

First/last day of month

First day of month:

D - EXTRACT(DAY FROM D) + 1;

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)

Процедуры для работы с датой и временем для InterBase6 (работоспособность проверена).

SET TERM #;

CREATE PROCEDURE ReturnYear(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(YEAR FROM ADate);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE Return_Day(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(DAY FROM ADate);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE Return_Month(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(MONTH FROM ADate);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE EncodeDate(Year_n INTEGER, Month_n INTEGER, Day_n INTEGER)

RETURNS (Result DATE)

AS

BEGIN

Result = CAST((Day_n || '.' || Month_n || '.' || Year_n) AS DATE);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE DecodeDate(ADate DATE)

RETURNS (Year_ INTEGER, Month_ INTEGER, Day_ INTEGER)

AS

BEGIN

EXECUTE PROCEDURE ReturnYear(ADate) RETURNING_VALUES Year_;

EXECUTE PROCEDURE Return_Month(ADate) RETURNING_VALUES Month_;

EXECUTE PROCEDURE Return_Day(ADate) RETURNING_VALUES Day_;

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE ElapsedDays(Date1 DATE, Date2 DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = Date2 - Date1;

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE DayOfWeek(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(WEEKDAY FROM ADate);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE Return_Time(ADate DATE)

RETURNS (RHour INTEGER, RMinute INTEGER)

AS

DECLARE VARIABLE RYear INTEGER;

DECLARE VARIABLE RMonth INTEGER;

DECLARE VARIABLE RDay INTEGER;

DECLARE VARIABLE WorkDate DATE;

DECLARE VARIABLE Percent DOUBLE PRECISION;

DECLARE VARIABLE WorkHour DOUBLE PRECISION;

BEGIN

EXECUTE PROCEDURE DecodeDate(ADate) RETURNING_VALUES(RYear, RMonth, RDay);

EXECUTE PROCEDURE EncodeDate(RYear, RMonth, RDay) RETURNING_VALUES(WorkDate);

Percent = ADate - WorkDate;

WorkHour = Percent *24;

RHour = WorkHour -0.5;

RMinute = (WorkHour - RHour) *60 -0.5;

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE DaysOfMonth(AYear INTEGER, AMonth INTEGER)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE WorkDate DATE;

BEGIN

Result = 31;

WHILE (Result > 28 AND WorkDate IS NULL) DO

BEGIN

EXECUTE PROCEDURE EncodeDate(AYear, AMonth, Result) RETURNING_VALUES(WorkDate);

WHEN ANY DO Result = Result - 1;

END

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE DayOfYear(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(YEARDAY FROM ADate);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE AddMonth(ADate DATE, AMonth INTEGER)

RETURNS (Result DATE)

AS

DECLARE VARIABLE RYear INTEGER;

DECLARE VARIABLE RMonth INTEGER;

DECLARE VARIABLE RDay INTEGER;

DECLARE VARIABLE Mod INTEGER;

DECLARE VARIABLE DaysOfMOnth INTEGER;

BEGIN

EXECUTE PROCEDURE DecodeDate(ADate) RETURNING_VALUES(RYear, RMonth, RDay);

IF(AMonth < 0) THEN

BEGIN

AMonth = AMonth * -1;

RYear = RYear - CAST(((AMonth - 1)/12) - 0.49 AS INTEGER) - 1;

AMonth = AMonth - 1;

EXECUTE PROCEDURE Modulus(AMonth, 12) RETURNING_VALUES(Mod);

AMonth = 12 - (Mod + 1);

RMonth = RMonth + AMonth;

END

ELSE

BEGIN

RYear = RYear + CAST(((AMonth - 1)/12) - 0.49 AS INTEGER);

AMonth = AMonth - 1;

EXECUTE PROCEDURE Modulus(AMonth, 12) RETURNING_VALUES(Mod);

RMonth = RMonth + Mod + 1;

END

IF(RMonth > 12) THEN

BEGIN

RYear = RYear + 1;

RMonth = RMonth - 12;

END

EXECUTE PROCEDURE DaysOfMonth(RYear, RMonth) RETURNING_VALUES (DaysOfMonth);

IF(RDay > DaysOfMonth) THEN RDay = DaysOfMonth;

EXECUTE PROCEDURE EncodeDate(RYear, RMonth, RDay) RETURNING_VALUES (Result);

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE DayOfWeekISO(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = EXTRACT(WEEKDAY FROM ADate - 1) + 1;

END

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE WeekOfYear(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = (EXTRACT(YEARDAY FROM ADate) - EXTRACT(WEEKDAY FROM ADate - 1) + 7) / 7;

END

#

SET TERM; #

SET TERM #;

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

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE Is_LeapYear_IB5 (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

#

SET TERM; #

SET TERM #;

CREATE PROCEDURE Is_LeapYear_d (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

#

SET TERM; #

SET TERM #;

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

#

SET TERM; #

/*------------------------------------------------------------

Date and time stored procedures

--------------------------------------------------------------

This script file contains the following procedures:

Year - Returns the year part of a date.

Month - Returns the month part of a date.

Day - Returns the day part of a date.

EncodeDate - Creates a date from its parts.

DecodeDate - Extracts a date to its parts.

ElapsedDays - Returns the number of days between two days.

Modulus - Returns the modulus of a division.

DayOfWeek - Returns the number of the day of week.

Time - Returns the hour and minute of a date.

DaysOfMonth - Returns the number of days in a given month.

DayOfYear - Returns the number of the day in the given year.

AddMonth - Adds given number of months to a date.

--------------------------------------------------------------

*/

SET TERM #;

/* The calculation is based on the following mathematical algorithm:

ElapsedDays = ElapsedYears*365 +(ElapsedYears/4) +(ElapsedYears/100) +(ElapsedYears/400)

ElapsedDays = ElapsedYears*365 +(ElapsedYears*97/400)

ElapsedDays = ElapsedYears*146097/400

ElapsedDays*400/146097 = ElapsedYears

*/

CREATE PROCEDURE Year(ADate DATE)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE ElapsedDays INTEGER;

DECLARE VARIABLE ElapsedYears INTEGER;

BEGIN

/* Lets go to the begin of the year */

WHILE(CAST(ADate AS VARCHAR(5)) <> "1-JAN") DO

ADate = ADate -1;

ElapsedDays = ADate -"1.1.1901";

Result = (ElapsedDays *400 /146097)+1901;

END

#

CREATE PROCEDURE Day(ADate DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = CAST(CAST(ADate AS VARCHAR(2)) AS INTEGER);

WHEN ANY DO Result = CAST(CAST(ADate AS VARCHAR(1)) AS INTEGER);

END

#

CREATE PROCEDURE Month(ADate DATE)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE WorkDate DATE;

DECLARE VARIABLE WorkDay INTEGER;

BEGIN

WorkDate = ADate;

Result = 1;

WHILE(CAST(WorkDate AS VARCHAR(5)) <> "1-JAN") DO

BEGIN

EXECUTE PROCEDURE Day(WorkDate) RETURNING_VALUES WorkDay;

IF(WorkDay = 1) THEN Result = Result+1;

WorkDate = WorkDate-1;

END

END

#

CREATE PROCEDURE EncodeDate(Year INTEGER, Month INTEGER, Day INTEGER)

RETURNS (Result DATE)

AS

BEGIN

Result = Day || "." || Month || "." || Year;

END

#

CREATE PROCEDURE DecodeDate(ADate DATE)

RETURNS (Year INTEGER, Month INTEGER, Day INTEGER)

AS

BEGIN

EXECUTE PROCEDURE Year(ADate) RETURNING_VALUES Year;

EXECUTE PROCEDURE Month(ADate) RETURNING_VALUES Month;

EXECUTE PROCEDURE Day(ADate) RETURNING_VALUES Day;

END

#

CREATE PROCEDURE ElapsedDays(Date1 DATE, Date2 DATE)

RETURNS (Result INTEGER)

AS

BEGIN

Result = Date2-Date1;

END

#

/* This procedure calculates the modulus of two number */

CREATE PROCEDURE Modulus(Dividend INTEGER, Divisor INTEGER)

RETURNS (Result INTEGER)

AS

BEGIN

IF(Dividend = 0) THEN Result = 0;

ELSE

Result = Dividend-(CAST((Dividend / Divisor)-0.5 AS INTEGER)*Divisor);

END

#

/* This procedure returns the number of the day of the week:

0 - Monday; 1 - Tuesday; 2 - Wdnesday; 3 - Thursday; 4 - Friday; 5 - Saturday; 6 - Sunday

*/

CREATE PROCEDURE DayOfWeek(ADate DATE)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE Elapsed INTEGER;

BEGIN

EXECUTE PROCEDURE ElapsedDays("1.1.96", ADate) RETURNING_VALUES Elapsed;

EXECUTE PROCEDURE Modulus(Elapsed, 7) RETURNING_VALUES Result;

END

#

CREATE PROCEDURE Time(ADate DATE)

RETURNS (Hour INTEGER, Minute INTEGER)

AS

DECLARE VARIABLE Year INTEGER;

DECLARE VARIABLE Month INTEGER;

DECLARE VARIABLE Day INTEGER;

DECLARE VARIABLE WorkDate DATE;

DECLARE VARIABLE Percent DOUBLE PRECISION;

DECLARE VARIABLE WorkHour DOUBLE PRECISION;

BEGIN

EXECUTE PROCEDURE DecodeDate(ADate) RETURNING_VALUES(Year, Month, Day);

EXECUTE PROCEDURE EncodeDate(Year, Month, Day) RETURNING_VALUES(WorkDate);

Percent = ADate - WorkDate;

WorkHour = Percent *24;

Hour = WorkHour -0.5;

Minute = (WorkHour - Hour) *60 -0.5;

END

#

CREATE PROCEDURE DaysOfMonth(AYear INTEGER, AMonth INTEGER)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE WorkDate DATE;

BEGIN

Result = 31;

WHILE (Result > 28 AND WorkDate IS NULL) DO

BEGIN

EXECUTE PROCEDURE EncodeDate(AYear, AMonth, Result) RETURNING_VALUES(WorkDate);

WHEN ANY DO Result = Result -1;

END

END

#

CREATE PROCEDURE DayOfYear(ADate DATE)

RETURNS (Result INTEGER)

AS

DECLARE VARIABLE WorkYear INTEGER;

DECLARE VARIABLE WorkMonth INTEGER;

DECLARE VARIABLE I INTEGER;

BEGIN

Result = 0;

EXECUTE PROCEDURE Year(ADate) RETURNING_VALUES(WorkYear);

EXECUTE PROCEDURE Month(ADate) RETURNING_VALUES(WorkMonth);

WorkMonth = WorkMonth-1;

WHILE (WorkMonth > 0) DO

BEGIN

EXECUTE PROCEDURE DaysOfMonth(WorkYear, WorkMonth) RETURNING_VALUES(I);

Result = Result+I;

WorkMonth = WorkMonth-1;

END

EXECUTE PROCEDURE Day(ADate) RETURNING_VALUES(I);

Result = Result +I;

END

#

/*

This procedure is based on the idea of George Kozaderov.

Address: 19-17, Engels st.,Ryazan, 390010, Russia

e-mail: relmail@geo.ryazan.ru

*/

CREATE PROCEDURE AddMonth(ADate DATE, AMonth INTEGER)

RETURNS (Result DATE)

AS

DECLARE VARIABLE Year INTEGER;

DECLARE VARIABLE Month INTEGER;

DECLARE VARIABLE Day INTEGER;

DECLARE VARIABLE Mod INTEGER;

DECLARE VARIABLE DaysOfMOnth INTEGER;

BEGIN

EXECUTE PROCEDURE DecodeDate(ADate) RETURNING_VALUES(Year, Month, Day);

IF(AMonth < 0) THEN

BEGIN

AMonth = AMonth * -1;

Year = Year - CAST(((AMonth-1)/12)-0.49 AS INTEGER) -1;

AMonth = AMonth -1;

EXECUTE PROCEDURE Modulus(AMonth, 12) RETURNING_VALUES(Mod);

AMonth = 12 - (Mod+1);

Month = Month + AMonth;

END

ELSE

BEGIN

Year = Year + CAST(((AMonth-1)/12)-0.49 AS INTEGER);

AMonth = AMonth -1;

EXECUTE PROCEDURE Modulus(AMonth, 12) RETURNING_VALUES(Mod);

Month = Month + Mod +1;

END

IF(Month > 12) THEN

BEGIN

Year = Year +1;

Month = Month -12;

END

EXECUTE PROCEDURE DaysOfMonth(Year, Month) RETURNING_VALUES (DaysOfMonth);

IF(Day > DaysOfMonth) THEN Day = DaysOfMonth;

EXECUTE PROCEDURE EncodeDate(Year, Month, Day) RETURNING_VALUES (Result);

END

#

SET TERM; #