| DotNetFirebird Using Firebird SQL in .NET. |
|
Home
Features
Download
Documentation
FAQ
Tools and Code
About
Blog
|
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
Previous
Archives
Copyright © 2005 - 2007 DotNetFirebird |