FreeAdhocUDF for InterBase and FireBird in deutschin englishen françaisen españolin italianoa português
homeprevious pageÜbersicht • overview • aperçunext pagelast page Seite empfehlen • advice website •Umfrage • poll • sondage mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprintsitemap
Date-time functions 103  functions
Preliminary note
Calculate 11 functions
Determin 49 functions
Format 18 functions
Compare 4 functions
Convert 7 functions
movable feasts 14 functions
returns <null> instead of 0, '' (empty string) or '17.11.1858'
sinceInterBase 6.0 this function is substitutable with a native SQL statement
since FireBird 1.0 this function is substitutable with a native SQL statement
since FireBird 2.1 this function is substitutable with a native SQL statement
Output RETURN mechanism if nothing other is published: FREE_IT
TestSQLs with NULL run only in FireBird 2.
 
Date-time functions: Preliminary note
For calculation of time there are used different dates. The most importent are
- Julian Date (JD)
- modified Julian Date (MJD)
- Dubliner Julian Date (DJD)
- ANSI Date
- UNIX Time (POSIX-Standard)

The Julian Date (JD) is a continuous count of days and fractions elapsed since 1st Januar -4712 12:00. For example the 1st Januar 2000 12:00 is the julian date 2.451.545,0.
The julian date did'nt mistake with a date in the julian calendar.
As a continious count of days the julian date is free of irregularities like leap days, different length of month a.s.o. you can find in most of calendars. It is used mostly in astronomy to describe time-dependents for easy calculation of time-differences.

In international geophysical year (1957/1958) a modified Julian Date (mJD oder MJD) was established with zeropoint at 1985-11-17 0:00 UT(worldtime): MJD = JD – 2.400.000,5. It was first used to record the orbit of Sputnik via an IBM 704 (36-bit machine). Digital Equipment Corporation (DEC) used MJD in their OS VMS and their database Rdb/VMS, on which Jim Starkey cooperated before he leaves DEC and programmed his own RDBMS (relational database management system) (Groton DataBase), which becomes InterBase.
MJD is mainly used in geodesy, geophysics and space flight, but not become accepted in astronomy.

The Dublin Julian Date (DJD) is another version of the Julian Date. The count of days started with the beginning of the year 1900 (used in Microsoft Excel, Lotus-123, Delphi) or the the year 1904 (Microsoft Excel for Mac OS). Because the count starts on 1st Januar with 1, the correct zeropoint is 1899-12-31 0:00. Some programms make more confusion because they use 1900 as a leap year and therefore their dates after 28th february 1900 are wrong (zeropoint form them is 1899-12-30 0:00).

The ANSI-Date starts with 1st januar 1601 as day „1“. It's used in COBOL.

Unix time, or POSIX time, is a system for describing points in time: it is the number of seconds elapsed since midnight UTC of January 1, 1970, not counting leap seconds. This starting-time is called The EPOCH. It is widely used not only on Unix-like operating systems but also in many other computing systems.

(look http://en.wikipedia.org/wiki/Julian_date and http://en.wikipedia.org/wiki/Unix_time)

InterBase and FireBird uses the modified Julian Date with 1858-11-17 as day 0.
 
Date-time functions: Calculate
F_ADDYEAR compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF

substitutable with DATEADD
Entrypoint addyear
Input TIMESTAMP
INTEGER
date optionally time
years to add
Output TIMESTAMP adds years to timestamp
If parameter 2 is negativ, years where subtract.
If the outputyear is a non-leapyear, for input 29th February the output is 1st March.
TestSQL
SELECT '01.10.2008 15:03:01' AS ISCORRECT, F_ADDYEAR('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '01.10.2002 15:03:01' AS ISCORRECT, F_ADDYEAR('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT '29.02.2008 15:03:01' AS ISCORRECT, F_ADDYEAR('29.02.2004 15:03:01', 4) FROM RDB$DATABASE;
SELECT '01.03.2009 15:03:01' AS ISCORRECT, F_ADDYEAR('29.02.2008 15:03:01', 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDYEAR(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDMONTH function from adhoc

substitutable with DATEADD
Entrypoint addmonth
Input TIMESTAMP
INTEGER
date optionally time
month to add
Output TIMESTAMP adds months to timestamp
If parameter 2 is negativ, months where subtract.
TestSQL
SELECT '01.03.2006 15:03:01' AS ISCORRECT, F_ADDMONTH('01.10.2005 15:03:01', 5) FROM RDB$DATABASE;
SELECT '01.07.2005 15:03:01' AS ISCORRECT, F_ADDMONTH('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDMONTH(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDWEEK function from adhoc

substitutable with DATEADD
Entrypoint addweek
Input TIMESTAMP
INTEGER
date optionally time
weeks to add
Output TIMESTAMP adds weeks to timestamp
If parameter 2 is negativ, weeks where subtract.
TestSQL
SELECT '22.10.2005 15:03:01' AS ISCORRECT, F_ADDWEEK('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '10.09.2005 15:03:01' AS ISCORRECT, F_ADDWEEK('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDWEEK(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDDAY function from adhoc

substitutable with DATEADD
Entrypoint addday
Input TIMESTAMP
INTEGER
date optionally time
days to add
Output TIMESTAMP adds days to timestamp
If parameter 2 is negativ, days where subtract.
TestSQL
SELECT '04.10.2005 15:03:01' AS ISCORRECT, F_ADDDAY('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '28.09.2005 15:03:01' AS ISCORRECT, F_ADDDAY('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDDAY(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDHOUR function from adhoc

substitutable with DATEADD
Entrypoint addhour
Input TIMESTAMP
INTEGER
date optionally time
hours to add
Output TIMESTAMP adds hours to timestamp
If parameter 2 is negativ, hours where subtract.
TestSQL
SELECT '01.10.2005 18:03:01' AS ISCORRECT, F_ADDHOUR('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '01.10.2005 12:03:01' AS ISCORRECT, F_ADDHOUR('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDHOUR(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDMINUTE function from adhoc

substitutable with DATEADD
Entrypoint addminute
Input TIMESTAMP
INTEGER
date optionally time
minutes to add
Output TIMESTAMP adds minutes to timestamp
If parameter 2 is negativ, minutes where subtract.
TestSQL
SELECT '01.10.2005 15:06:01' AS ISCORRECT, F_ADDMINUTE('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '01.10.2005 15:00:01' AS ISCORRECT, F_ADDMINUTE('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDMINUTE(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ADDSECOND function from adhoc

substitutable with DATEADD
Entrypoint addsecond
Input TIMESTAMP
INTEGER
date optionally time
seconds to add
Output TIMESTAMP adds seconds to timestamp
If parameter 2 is negativ, seconds where subtract.
TestSQL
SELECT '01.10.2005 15:03:04' AS ISCORRECT, F_ADDSECOND('01.10.2005 15:03:01', 3) FROM RDB$DATABASE;
SELECT '01.10.2005 15:02:58' AS ISCORRECT, F_ADDSECOND('01.10.2005 15:03:01', -3) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDSECOND(NULL, NULL) FROM RDB$DATABASE;  
nach oben • go top •
F_INCDATE input/output-compatibility to rFunc (INCDATE)
Entrypoint incdate
Input TIMESTAMP
INTEGER
INTEGER
INTEGER
date optionally time
days to add
month to add
years to add
Output TIMESTAMP Adds to parameter 1 days, month and years.
If parameters are negative they will be subtracted.
TestSQL
SELECT '02.11.2006 15:03:05' AS ISCORRECT, F_INCDATE('01.10.2005 15:03:05', 1, 1, 1) FROM RDB$DATABASE;
SELECT '31.08.2004 15:03:05' AS ISCORRECT, F_INCDATE('01.10.2005 15:03:05', -1, -1, -1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_INCDATE(NULL, NULL, NULL, NULL) FROM RDB$DATABASE;  
nach oben • go top •
F_INCDATETIME input/output-compatibility to rFunc (INCDATETIME)
Entrypoint incdatetime
Input TIMESTAMP
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
date optionally time
days to add
month to add
years to add
hours to add
minutes to add
seconds to add
Output TIMESTAMP Adds to parameter 1 days, month, years, hours, minutes and seconds.
If parameters are negative they will be subtracted.
TestSQL
SELECT '02.11.2006 16:04:06' AS ISCORRECT, F_INCDATETIME('01.10.2005 15:03:05', 1, 1, 1, 1, 1, 1) FROM RDB$DATABASE;
SELECT '02.11.2006 14:02:04' AS ISCORRECT, F_INCDATETIME('01.10.2005 15:03:05', 1, 1, 1, -1, -1, -1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_INCDATETIME(NULL, NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;  
nach oben • go top •
F_ADDPERIOD function from adhoc
Entrypoint addperiod
Input CSTRING(254)
CSTRING(254)
period 1 in pattern [d]:h:m:s
period 2 in pattern [d]:h:m:s
Output CSTRING(254) sum of period 1 and 2
Periods could be enter with 2- or 1digits interval (dd:hh:mm:ss or d:h:m:s).
Negative periods starts with a "-" in front.
Periods with pattern days:hours:minutes:seconds (4 intervalls) or hours:minutes:seconds (3 intervalls) are allowed.
The output of periods with less than 3 intervalls or containing other characters is <null> or empty string.
For input also f.e. '0:26:0:0' instead of '1:2:0:0' alllowed for 26 hours.
The output is allways in pattern days:hours:minutes:seconds (4 * 2 digit intervalls)
TestSQL
SELECT '00:01:25:10' AS ISCORRECT, F_ADDPERIOD('0:1:10:0', '0:0:15:10') FROM RDB$DATABASE;
SELECT '00:01:25:10' AS ISCORRECT, F_ADDPERIOD('01:10:00', '00:15:10') FROM RDB$DATABASE;
SELECT '00:00:54:50' AS ISCORRECT, F_SUBPERIOD('0:1:10:0', '0:0:15:10'), F_ADDPERIOD('0:1:10:0', '-0:0:15:10') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ADDPERIOD(NULL, NULL) FROM RDB$DATABASE;  
nach oben • go top •
F_SUBPERIOD function from adhoc
Entrypoint subperiod
Input CSTRING(254)
CSTRING(254)
period 1 in pattern [d]:h:m:s
period 2 in pattern [d]:h:m:s
Output CSTRING(254) period 2 subtracted from period 1
Periods could be enter with 2- or 1digits interval (dd:hh:mm:ss or d:h:m:s).
Negative periods starts with a "-" in front.
Periods with pattern days:hours:minutes:seconds (4 intervalls) or hours:minutes:seconds (3 intervalls) are allowed.
The output of periods with less than 3 intervalls or containing other characters is <null> or empty string.
For input also f.e. '0:26:0:0' instead of '1:2:0:0' alllowed for 26 hours.
The output is allways in pattern days:hours:minutes:seconds (4 * 2 digit intervalls).
If second input period is greater than first, the output is negativ ("-" in front).
TestSQL
SELECT '00:00:54:50' AS ISCORRECT, F_SUBPERIOD('0:1:10:0', '0:0:15:10') FROM RDB$DATABASE;
SELECT '-00:00:54:50' AS ISCORRECT, F_SUBPERIOD('0:0:15:10', '0:1:10:0') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_SUBPERIOD(NULL, NULL) FROM RDB$DATABASE;  
nach oben • go top •
 
Date-time functions: Determin
To calculate the difference between two timestamps there are 3 posibilities (can be used with all IB and FB-versions)
  • if you want a negative number if the first date is newer than the second date
    • use F_AGEIN... with the entrypoint agein...
  • if you want <null> if the first date is newer than the second date
    • use F_AGEIN...N with the entrypoint agein...null 
  • if you want every time a positv number in every case of input
    • use F_...BETWEEN with the entrypoint ...between
Input for all
TIMESTAMP
TIMESTAMP
(older) date optionally time 1
(newer) date optionally time 2
F_AGEINYEARS
function from adhoc

substitutable with DATEDIFF
Entrypoint ageinyears
Output INTEGER difference in (integer) years between timestamp 1 and timestamp 2
F_AGEINYEARSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinyearsnull
Output INTEGER difference in (integer) years between timestamp 1 and timestamp 2
F_AGEINMONTHS
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF

substitutable with DATEDIFF
Entrypoint ageinmonths
Output INTEGER difference in (integer) months between timestamp 1 and timestamp 2
F_AGEINMONTHSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinmonthsnull
Output INTEGER difference in (integer) months between timestamp 1 and timestamp 2
F_AGEINWEEKS
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP

substitutable with DATEDIFF
Entrypoint ageinweeks
Output INTEGER difference in (integer) weeks between timestamp 1 and timestamp 2
Sunday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINWEEKSISO
function from adhoc
Entrypointageinweeksiso
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
ISO-version of F_AGEINWEEKS. Monday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINWEEKSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinweeksnull
Output INTEGER difference in (integer) weeks between timestamp 1 and timestamp 2
Sunday is 1st day of week.
If the two dates are in the same week, the return value is 0
F_AGEINWEEKSNISOfunction from adhoc


Entrypoint ageinweeksnulliso
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
ISO-version of F_AGEINWEEKSN. Monday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINDAYS
 
compatibility to FreeUDFLib, FreeUDFLib AvERP
input/output-compatibility to rFunc (DAYSBETWEEN)

substitutable with DATEDIFF
Entrypoint ageindays
OutputINTEGERdifference in (integer) days between timestamp 1 and timestamp 2
F_AGEINDAYSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageindaysnull
Output INTEGER difference in (integer) days between timestamp 1 and timestamp 2
F_AGEINHOURS
function from adhoc

substitutable with DATEDIFF
Entrypoint ageinhours
OutputINTEGERdifference in (integer) hours between timestamp 1 and timestamp 2
F_AGEINHOURSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinhoursnull
Output INTEGER difference in (integer) hours between timestamp 1 and timestamp 2
F_AGEINMINUTES
function from adhoc

substitutable with DATEDIFF
Entrypoint ageinminutes
OutputINTEGERdifference in (integer) minutes between timestamp 1 and timestamp 2
F_AGEINMINUTESN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinminutesnull
Output INTEGER difference in (integer) minutes between timestamp 1 and timestamp 2
F_AGEINSECONDS
function from adhoc

substitutable with DATEDIFF
Entrypoint ageinseconds
OutputINTEGERdifference in (integer) seconds between timestamp 1 and timestamp 2
F_AGEINSECONDSN function from adhoc

substitutable with DATEDIFF
Entrypoint ageinsecondsnull
Output INTEGER difference in (integer) seconds between timestamp 1 and timestamp 2
For compatibility reasons:
If date 2 < date 1 the result is negativ. The correct result had be 0 than there is no negative age!
Use F_AGEIN...N or F_...BETWEEN if you don't want a negative age.
TestSQL
SELECT 3 AS ISCORRECT, F_AGEINYEARS('01.01.2005 15:01:21','01.10.2008 15:01:01') FROM RDB$DATABASE;
SELECT -3 AS ISCORRECT, F_AGEINYEARS('01.01.2005 15:01:21','01.10.2002 15:01:01') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINYEARS(NULL, NULL) FROM RDB$DATABASE;
SELECT 9 AS ISCORRECT, F_AGEINMONTHS('01.01.2005 15:01:21','01.10.2005 15:01:01') FROM RDB$DATABASE;
SELECT -9 AS ISCORRECT, F_AGEINMONTHS('01.10.2005 15:01:21','01.01.2005 15:01:01') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINMONTHS(NULL, NULL) FROM RDB$DATABASE;
SELECT 20 AS ISCORRECT, F_AGEINWEEKS('01.01.2005 15:01:21','15.05.2005 15:01:21') FROM RDB$DATABASE;
SELECT -33 AS ISCORRECT, F_AGEINWEEKS('01.01.2006 15:01:21','15.05.2005 15:01:21') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINWEEKS(NULL, NULL) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINDAYS('01.10.2005 15:01:03','11.10.2005 15:04:03') FROM RDB$DATABASE;
SELECT -20 AS ISCORRECT, F_AGEINDAYS('01.10.2005 15:01:03','11.09.2005 15:04:03') FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_AGEINHOURS('01.10.2005 15:01:03','01.10.2005 18:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINDAYS(NULL, NULL) FROM RDB$DATABASE;
SELECT 14 AS ISCORRECT, F_AGEINMINUTES('01.10.2005 15:01:03','01.10.2005 15:15:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINHOURS(NULL, NULL) FROM RDB$DATABASE;
SELECT 14 AS ISCORRECT, F_AGEINMINUTES('01.10.2005 15:01:03','01.10.2005 15:15:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINMINUTES(NULL, NULL) FROM RDB$DATABASE;
SELECT 20 AS ISCORRECT, F_AGEINSECONDS('01.01.2005 15:01:01','01.01.2005 15:01:21') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINSECONDS(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
Input for all
TIMESTAMP
TIMESTAMP
INTEGER
INTEGER
INTEGER
INTEGER
(older) date optionally time 1
(newer) date optionally time 2
min. value
min. value is used (0 = no, 1 = yes)
max. value
max. value is used (0 = no, 1 = yes)
F_AGEINYEARSTHRESHOLD
function from adhoc
Entrypoint ageinyearsthreshold
OutputINTEGERdifference in (integer) years between timestamp 1 and timestamp 2
F_AGEINYEARSTHRESHOLDN function from adhoc
Entrypoint ageinyearsthresholdnull
Output INTEGER difference in (integer) years between timestamp 1 and timestamp 2
F_AGEINMONTHSTHRESHOLD
compatibility to FreeUDFLib, FreeUDFLib AvERP
Entrypoint ageinmonththreshold
OutputINTEGERdifference in (integer) months between timestamp 1 and timestamp 2
F_AGEINMONTHSTHRESHOLDN function from adhoc
Entrypoint ageinmonththresholdnull
Output INTEGER difference in (integer) months between timestamp 1 and timestamp 2
F_AGEINWEEKSTHRESHOLD
compatibility to FreeUDFLib, FreeUDFLib AvERP
Entrypoint ageinweeksthreshold
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
Sunday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINWEEKSTHRESHOLDISO
function from adhoc
Entrypointageinweeksthresholdiso
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
ISO-version of F_AGEINWEEKSTHRESDHOLD. Monday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINWEEKSTHRESHOLDN function from adhoc
Entrypoint ageinweeksthresholdnull
Output INTEGER difference in (integer) weeks between timestamp 1 and timestamp 2
Sunday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINWEEKSTHRESHOLDNISOfunction from adhoc
Entrypoint ageinweeksthresholdnulliso
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
ISO-version of F_AGEINWEEKSTHRESDHOLDN. Monday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_AGEINDAYSTHRESHOLD
compatibility to FreeUDFLib, FreeUDFLib AvERP
Entrypoint ageindaysthreshold
OutputINTEGERdifference in (integer) days between timestamp 1 and timestamp 2
F_AGEINDAYSTHRESHOLDN function from adhoc
Entrypoint ageindaysthresholdnull
Output INTEGER difference in (integer) days between timestamp 1 and timestamp 2
F_AGEINHOURSTHRESHOLD
function from adhoc
Entrypoint ageinhoursthreshold
OutputINTEGERdifference in (integer) hours between timestamp 1 and timestamp 2
F_AGEINHOURSTHRESHOLDN function from adhoc
Entrypoint ageinhoursthresholdnull
Output INTEGER difference in (integer) hours between timestamp 1 and timestamp 2
F_AGEINMINUTESTHRESHOLD
function from adhoc
Entrypoint ageinminutesthreshold
OutputINTEGERdifference in (integer) minutes between timestamp 1 and timestamp 2
F_AGEINMINUTESTHRESHOLDN function from adhoc
Entrypoint ageinminutesthresholdnull
Output INTEGER difference in (integer) minutes between timestamp 1 and timestamp 2
F_AGEINSECONDSTHRESHOLD  function from adhoc
Entrypoint ageinsecondsthreshold
OutputINTEGERdifference in (integer) seconds between timestamp 1 and timestamp 2
F_AGEINSECONDSTHRESHOLDN function from adhoc
Entrypoint ageinsecondsthresholdnull
Output INTEGER difference in (integer) seconds between timestamp 1 and timestamp 2
Is parameter 4 (use min. value) set to 1, the result is minimum the value of parameter 3.
Is parameter 6 (use max. value) set to 1, the result is maximum the value of parameter 5.
For compatibility reasons:
If date 2 < date 1 the result is negativ. The correct result had be 0 than there is no negative age!
Use F_AGEIN...N or F_...BETWEEN if you don't want a negative age.
TestSQL
SELECT 3 AS ISCORRECT, F_AGEINYEARSTHRESHOLD('01.10.2005 15:01:03','01.12.2008 15:03:03', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_AGEINYEARSTHRESHOLD('01.10.2005 15:01:03','01.12.2008 15:03:03', 5, 1, 10, 0) FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_AGEINYEARSTHRESHOLD('01.10.2005 15:01:03','01.12.2008 15:03:03', 5, 0, 10, 1) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINYEARSTHRESHOLD('01.10.2005 15:01:03','01.12.2018 15:03:03', 5, 0, 10, 1) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINYEARSTHRESHOLD('01.10.2005 15:01:03','01.12.2018 15:03:03', 5, 1, 10, 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINYEARSTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_AGEINMONTHSTHRESHOLD('01.10.2005 15:01:03','01.12.2005 15:03:03', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_AGEINMONTHSTHRESHOLD('01.10.2005 15:01:03','01.12.2005 15:03:03', 5, 1, 10, 0) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINMONTHSTHRESHOLD('01.01.2005 15:01:03','01.12.2005 15:03:03', 5, 1, 10, 1) FROM RDB$DATABASE;
SELECT -1 AS ISCORRECT, F_AGEINMONTHSTHRESHOLD('01.01.2006 15:01:03','01.12.2005 15:03:03', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINMONTHSTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_AGEINWEEKSTHRESHOLD('01.01.2005 15:01:21','15.01.2005 15:01:21', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_AGEINWEEKSTHRESHOLD('01.01.2005 15:01:21',’15.01.2005 15:01:21', 5, 1, 10, 0) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINWEEKSTHRESHOLD('01.01.2005 15:01:21','15.05.2005 15:01:21', 5, 0, 10, 1) FROM RDB$DATABASE;           
SELECT -33 AS ISCORRECT, F_AGEINWEEKSTHRESHOLD('01.01.2006 15:01:21','15.05.2005 15:01:21', 5, 0, 10, 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINWEEKSTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','11.10.2005 15:01:03', 15, 0, 20, 0) FROM RDB$DATABASE;
SELECT 15 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','11.10.2005 15:01:03', 15, 1, 20, 0) FROM RDB$DATABASE;
SELECT 20 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','01.11.2005 15:01:03', 15, 0, 20, 1) FROM RDB$DATABASE;
SELECT 20 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','01.11.2005 15:01:03', 15, 1, 20, 1) FROM RDB$DATABASE;
SELECT 15 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','01.09.2005 15:01:03', 15, 1, -20, 1) FROM RDB$DATABASE;
SELECT -20 AS ISCORRECT, F_AGEINDAYSTHRESHOLD('01.10.2005 15:01:03','15.09.2005 15:01:03', 15, 0, -20, 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINDAYSTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_AGEINHOURSTHRESHOLD('01.10.2005 15:01:03','01.10.2005 18:01:03', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_AGEINHOURSTHRESHOLD('01.10.2005 15:01:03','01.10.2005 18:01:03', 5, 1, 10, 0) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINHOURSTHRESHOLD('01.10.2005 15:01:03','02.10.2005 18:01:03', 5, 1, 10, 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINHOURSTHRESHOLD(NULL) FROM RDB$DATABASE;
SELECT 14 AS ISCORRECT, F_AGEINMINUTESTHRESHOLD('01.10.2005 15:01:03','01.10.2005 15:15:03', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINMINUTESTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
SELECT 16 AS ISCORRECT, F_AGEINSECONDSTHRESHOLD('01.01.2005 15:01:03','01.01.2005 15:01:19', 5, 0, 10, 0) FROM RDB$DATABASE;
SELECT 5 AS ISCORRECT, F_AGEINSECONDSTHRESHOLD('01.01.2005 15:01:19','01.01.2005 15:01:21', 5, 1, 10, 0) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_AGEINSECONDSTHRESHOLD('01.01.2005 15:01:03','01.01.2005 15:01:19', 5, 1, 10, 1) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_AGEINSECONDSTHRESHOLD(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
Input for all

TIMESTAMP
TIMESTAMP
date optionally time 1
date optionally time 2
F_YEARSBETWEEN compatibility to GrUDF
Entrypoint yearsbetween
Output INTEGER difference in (integer) years between timestamp 1 and timestamp 2
F_MONTHSBETWEEN function from adhoc
Entrypoint monthsbetween
Output INTEGER difference in (integer) months between timestamp 1 and timestamp 2
F_WEEKSBETWEEN compatibility to GrUDF
Entrypoint weeksbetween
Output INTEGER difference in (integer) weeks between timestamp 1 and timestamp 2
Sunday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_WEEKSBETWEENISOfunction from adhoc
Entrypoint weeksbetweeniso
OutputINTEGERdifference in (integer) weeks between timestamp 1 and timestamp 2
ISO-version of F_WEEKSBETWEEN. Monday is 1st day of week.
If the two dates are in the same week, the return value is 0.
F_DAYSBETWEEN function from adhoc
Entrypoint daysbetween
Output INTEGER difference in (integer) days between timestamp 1 and timestamp 2
F_HOURSBETWEEN compatibility to GrUDF
Entrypoint hoursbetween
Output INTEGER difference in (integer) hours between timestamp 1 and timestamp 2
F_MINUTESBETWEEN compatibility to GrUDF
Entrypoint minutesbetween
Output INTEGER difference in (integer) minutes between timestamp 1 and timestamp 2
F_SECONDSBETWEEN compatibility to GrUDF
Entrypoint secondsbetween
Output INTEGER difference in (integer) seconds between timestamp 1 and timestamp 2
The result is always positiv values in difference to F_AGEIN.. which can also return negativ values.
TestSQL
SELECT 2 AS ISCORRECT, F_YEARSBETWEEN('01.10.2005 15:01:03','11.10.2007 15:01:03') FROM  RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_YEARSBETWEEN('11.10.2007 15:01:03','01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_YEARSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 1 AS ISCORRECT, F_MONTHSBETWEEN('01.10.2005 15:01:03','11.11.2005 15:01:03') FROM RDB$DATABASE;
SELECT 1 AS ISCORRECT, F_MONTHSBETWEEN('11.11.2005 15:01:03','01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MONTHSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_WEEKSBETWEEN('01.10.2005 15:01:03','11.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT 2 AS ISCORRECT, F_WEEKSBETWEEN('11.10.2005 15:01:03',’01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WEEKSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_DAYSBETWEEN('01.10.2005 15:01:03','11.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT 10 AS ISCORRECT, F_DAYSBETWEEN('11.10.2005 15:01:03','01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DAYSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 240 AS ISCORRECT, F_HOURSBETWEEN('01.10.2005 15:01:03','11.10.2005 15:04:03') FROM RDB$DATABASE;
SELECT 240 AS ISCORRECT, F_HOURSBETWEEN('11.10.2005 15:04:03','01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_HOURSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_MINUTESBETWEEN('01.10.2005 15:01:03','01.10.2005 15:04:03') FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_MINUTESBETWEEN('01.10.2005 15:04:03', '01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MINUTESBETWEEN(NULL, NULL) FROM RDB$DATABASE;
SELECT 180 AS ISCORRECT, F_SECONDSBETWEEN('01.10.2005 15:01:03','01.10.2005 15:04:03') FROM RDB$DATABASE;
SELECT 180 AS ISCORRECT, F_SECONDSBETWEEN('01.10.2005 15:04:03','01.10.2005 15:01:03') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_SECONDSBETWEEN(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DAYOFYEAR
F_EXTRACTYEARDAY
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (EXTRACTYEARDAY)
ersetzbar durch EXTRACT(YEARDAY FROM ...)
Entrypoint dayofyear
Input TIMESTAMP date optionally time 
Output INTEGER number of days (day number x of the year) up to the selected timestamp
F_DAYOFYEAR: counting starts with 1
F_EXTRACTYEARDAY: counting starts with 0

TestSQL
SELECT 235 AS ISCORRECT, F_DAYOFYEAR('22.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DAYOFYEAR(NULL) FROM RDB$DATABASE;
SELECT 234 AS ISCORRECT, F_EXTRACTYEARDAY('22.08.2004'), F_DAYOFYEAR('22.08.2004') FROM RDB$DATABASE;
nach oben • go top •
F_DAYOFMONTH
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (EXTRACTDAY)
ersetzbar durch EXTRACT(DAY FROM ...)
Entrypoint dayofmonth
Input TIMESTAMP date optionally time 
Output INTEGER day of the month
Counting starts with 1
TestSQL
SELECT 23 AS ISCORRECT, F_DAYOFMONTH('23.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DAYOFMONTH(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DAYOFWEEK
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUD
Entrypoint dayofweek
F_EXTRACTWEEKDAY input/output-compatibility to rFunc (EXTRACTWEEKDAY)
Entrypoint r_weekday
F_DOW input/output-compatibility to rFunc (DOW)
Entrypoint r_dow
ersetzbar durch EXTRACT(WEEKDAY FROM ...)
Entrypoint
Input TIMESTAMP date optionally time 
Output INTEGER day of the week as integer
F_DAYOFWEEK: Sunday = 1, Monday = 2, Thuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7
F_EXTRACTWEEKDAY: Sunday = 0, Monday = 1, Thuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6
F_DOW: Sunday = 7, Monday = 1, Thuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6
TestSQL
SELECT 1 AS ISCORRECT, F_DAYOFWEEK('22.08.2004') FROM RDB$DATABASE;
SELECT NULL  AS ISCORRECT, F_DAYOFWEEK(NULL) FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_EXTRACTWEEKDAY('22.08.2004'), F_DAYOFWEEK('22.08.2004') FROM RDB$DATABASE;
SELECT 7 AS ISCORRECT, F_DOW('22.08.2004') FROM RDB$DATABASE;
nach oben • go top •
F_DAYSOFMONTH function from adhoc
input/output-compatibility to rFunc (DAYPERMONTH)
Entrypoint daysofmonth
Input INTEGER
INTEGER
month
year
Output INTEGER amount of days (last day) in the month of the year
Counting starts with 1
TestSQL
SELECT 29 AS ISCORRECT, F_DAYSOFMONTH(2, 2004) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DAYSOFMONTH(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_LASTDAY compatibility to GrUDF
Entrypoint lastday
Input INTEGER
INTEGER
year
month
Output INTEGER last day in the month of the year
Counting starts with 1
(nearly) identcall to F_DAYSOFMONTH, only swapped parameters
TestSQL
SELECT 29 AS ISCORRECT, F_LASTDAY(2004, 2) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_LASTDAY(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_FIRSTDAYMONTH input/output-compatibility to rFunc (FIRSTDAYMONTH)
Entrypoint firstdaymonth
Input TIMESTAMP date optionally time
Output TIMESTAMP Date with 1. as day and time 00:00:00
TestSQL
SELECT '01.01.2007 00:00:00' AS ISCORRECT, F_FIRSTDAYMONTH('03.01.2007 15:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_FIRSTDAYMONTH(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_LASTDAYMONTH input/output-compatibility to rFunc (LASTDAYMONTH)
Entrypoint lastdaymonth
Input TIMESTAMP date optionally time
Output TIMESTAMP Date with last of the month as day and time 00:00:00
TestSQL
SELECT '31.01.2007 00:00:00' AS ISCORRECT, F_LASTDAYMONTH('03.01.2007 15:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_LASTDAYMONTH(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DTIME function from adhoc
Entrypoint dtime
Input TIMESTAMP date optionally time 
Output INTEGER amount of days between the timestamp and 1899-12-31
Counting starts at 0.
TestSQL
SELECT 2 AS ISCORRECT, F_DTIME('03.01.1900') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DTIME(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ISLEAPYEAR compatibility to FreeUDFLibC
Entrypoint isleapyear
Input TIMESTAMP date optionally time 
Output INTEGER 1 = is leapyear, 0 = is no leapyear
Y2k-compatible (2000 is a leapyear).
TestSQL
SELECT 1 AS ISCORRECT, F_ISLEAPYEAR('22.08.2000 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ISLEAPYEAR(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_RISLEAPYEAR input/output-compatibility to rFunc (ISLEAPYEAR)
Entrypoint r_isleapyear
Input INTEGER year
Output INTEGER 1 = is leapyear, 0 = is no leapyear
Y2k-compatible (2000 is a leapyear).
TestSQL
SELECT 1 AS ISCORRECT, F_RISLEAPYEAR(2000) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_RISLEAPYEAR(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ZEITDIFFERENZ compatibility to FreeUDFLib AvERP, GrUDF
Entrypoint zeitdifferenz
Input TIMESTAMP
TIMESTAMP
CSTRING(1)
date optionally time 1
date optionally time 2
type of output
                    t = time between in days
                    h = time between in hours
                    m = time between in minutes
                    s = time between in seconds
                    alle other values return 0
Output DOUBLE time between date 1 and 2 as floatingpoint of the chosen parameter 3
TestSQL
SELECT 1.000 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 15:00:00', '01.10.2005 15:00:00', 't') FROM RDB$DATABASE;
SELECT 1.125 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 18:00:00', '01.10.2005 15:00:00', 't') FROM RDB$DATABASE;
SELECT 26.500 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 18:00:00', '01.10.2005 15:30:00', 'h') FROM RDB$DATABASE;
SELECT 1589.500 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 18:00:00', '01.10.2005 15:30:30', 'm') FROM RDB$DATABASE;
SELECT 95370.000 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 18:00:00', '01.10.2005 15:30:30', 's') FROM RDB$DATABASE;
SELECT 0.000 AS ISCORRECT, F_ZEITDIFFERENZ('02.10.2005 18:00:00', '01.10.2005 15:30:30', 'x') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ZEITDIFFERENZ(NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_COUNTWEEKDAYS function from adhoc
Entrypoint countweekdays
Input TIMESTAMP1
TIMESTAMP2
INTEGER
date optionally time 1
date optionally time 2
No. of weekday
Output INTEGER number of choosen weekdays between date 1 and date 2 
Monday = 1, Thuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, Sunday = 7
Counts number of choosen weekdays between date 1 and date 2 
TestSQL
SELECT 4 AS ISCORRECT, F_COUNTWEEKDAYS('04.02.2008', '27.02.2008', 2) FROM RDB$DATABASE;
SELECT 4 AS ISCORRECT, F_COUNTWEEKDAYS('05.02.2008', '26.02.2008', 2) FROM RDB$DATABASE;
SELECT 3 AS ISCORRECT, F_COUNTWEEKDAYS('06.02.2008', '27.02.2008', 2) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_COUNTWEEKDAYS(NULL, NULL, NULL) FROM RDB$DATABASE;

nach oben • go top •
Preliminary note to counting the week of the year - calendar week:
The year contains minimum 52 serially weeks (calender weeks). There are different versions to count. The first week of the year is the week
    * which contains the 1st January (USA, Excel-function)
    * the first week which contains minimum 4 days of the new year (DIN 1355 / ISO 8601)
    * the first complet week of the year (seldom)
The international norm ISO 8601 (1973) appoints monday as starting day of the week. Since 1976 monday is the first day of the week in Germany (DIN 1355). In 1978 the UNO decided, that Monday is the first day of the week.
The results of this are the following rules:
    * every Monday and only on Monday a new calendar week starts
    * the first calendar week of the year ist the week which contains minimum 4 days of the new year
The results of these rules are following characteristics:
    * there are no uncomplete calender weeks, without fail every week contains exactly 7 days
    * every year contains 52 or 53 calendar weeks
    * if a year starts or ends with Thursday, the year has 53 calendar weeks
    * 29th, 30th and 31st of December could belong to the first calendar week of the following year
    * 1st, 2nd and 3rd of January could belong to the last calendar week of the previous year
In many places of the world (f.e. North-America, Australia) there is still the tradition of Jewry and Christianity where Sunday ist the first day of the week. In USA and other countries there are the following rules:
    * every Sunday starts an new calendar week
    * the first calendar week of the year starts on 1. January
The result of these rules are following characteristics:
    * the first and the last calendar week of the year must not be complete, they can have less than 7 days
    * every year contains 53 calendar weeks
    * if the 31st of December is Sunday and the 1st of January of the same year was no Sunday, so this Sunday is the only day in the 54 calendar week. (This constellation happens very seldom, last in 2000, next in 2028).
Unfortunately Gregory Deatz had not read the rules exactly, so some functions are not standard of USA neather ISO (counts with week starting with Sunday). This is for functions F_YEAROFYEAR, F_WEEKOFYEAR and F_WOY. Please do not use this functions.
Functions F_YEAR and F_WEEK are USA standard, functions F_KALENDERWOCHE and F_WOYISO are standard ISO.
F_YEAR
F_YEAROFYEAR (don't use)
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
compatibility to FreeUDFLibC
input/output-compatibility to rFunc (EXTRACTYEAR)
ersetzbar durch EXTRACT(YEAR FROM ...)
Entrypoint year
Input TIMESTAMP date optionally time 
Output INTEGER year of the timestamp
F_YEAR counts with USA-Norm: week starting with Sunday, week 1 contains 1st January.
F_YEAROFYEAR counts wrong (not ISO): week starting with Sunday, week 1 starts with Sunday to Wednesday
TestSQL
SELECT 2004 AS ISCORRECT, F_YEAR(' 22.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_YEAR(NULL) FROM RDB$DATABASE;
SELECT 2004 AS ISCORRECT, F_YEAROFYEAR(' 22.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_YEAROFYEAR(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_QUARTER compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (QUARTER)
Entrypoint quarter
Input TIMESTAMP date optionally time 
Output INTEGER quarter of the timestamp
TestSQL
SELECT 3 AS ISCORRECT, F_QUARTER('23.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_QUARTER(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_MONTH compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (EXTRACTMONTH)
ersetzbar durch EXTRACT(MONTH FROM ...)
Entrypoint month
Input TIMESTAMP date optionally time 
Output INTEGER month of the timestamp
TestSQL
SELECT 8 AS ISCORRECT, F_MONTH('23.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MONTH(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_WEEK
F_WEEKOFYEAR (don't use)
compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
compatibility to FreeUDFLibC
Entrypoint week
Input TIMESTAMP date optionally time 
Output INTEGER the week of the timestamp
Counting like used in USA: Counting starts at week 1 which contains the 1st January.
TestSQL
SELECT 52 AS ISCORRECT, F_WEEK('28.12.2003') FROM RDB$DATABASE;
SELECT 52 AS ISCORRECT, F_WEEK('29.12.2003') FROM RDB$DATABASE;
SELECT 1 AS ISCORRECT, F_WEEK('01.01.2005') FROM RDB$DATABASE;
SELECT 41 AS ISCORRECT, F_WEEK('02.10.2005 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WEEK(NULL) FROM RDB$DATABASE;
SELECT 34 AS ISCORRECT, F_WEEKOFYEAR('22.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WEEKOFYEAR(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_WOY (don't use) compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
Entrypoint woy
Input TIMESTAMP date optionally time 
Output CSTRING(6) year and week as string
Counts like Gregory Deatz the year and week of year (week starting with Sunday - see above).
TestSQL
SELECT '200434' AS ISCORRECT, F_WOY('22.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WOY(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_WOYISO function from adhoc
Entrypoint woyiso
Input TIMESTAMP date optionally time
Ouput CSTRING(6) 4 digits year and 2 digits week of year as a string
Counts like ISO the year and week of year. If 1st January is in week 52, 53 or 54 of the previous year, the output year is also the previous year.
SELECT '200453' AS ISCORRECT, F_WOYISO('01.01.2005') FROM RDB$DATABASE;
SELECT '200539' AS ISCORRECT, F_WOYISO('02.10.2005 14:38:12') FROM RDB$DATABASE;
SELECT F_WOYISO('22.08.2009 14:38:12'), F_WOY('22.08.2009 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WOYISO(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_KALENDERWOCHE function from adhoc
Entrypoint week
Input TIMESTAMP date optionally time 
Output INTEGER the week of the timestamp based on ISO 8601
Counting based on ISO 8601: The counting starts in the week, which contains minimum 4 days of the new year.
TestSQL
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('31.12.1992') FROM RDB$DATABASE;
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('31.12.1998') FROM RDB$DATABASE;
SELECT 52 AS ISCORRECT, F_KALENDERWOCHE('28.12.2003') FROM RDB$DATABASE;
SELECT 1 AS ISCORRECT, F_KALENDERWOCHE('29.12.2003') FROM RDB$DATABASE;
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('31.12.2004') FROM RDB$DATABASE;
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('01.01.2005') FROM RDB$DATABASE;
SELECT 39 AS ISCORRECT, F_KALENDERWOCHE('02.10.2005') FROM RDB$DATABASE;
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('31.12.2009') FROM RDB$DATABASE;
SELECT 53 AS ISCORRECT, F_KALENDERWOCHE('31.12.2015') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_KALENDERWOCHE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_HOUR compatibility to GrUDF
input/output-compatibility to rFunc (EXTRACTHOUR)
ersetzbar durch EXTRACT(HOUR FROM ...)
Entrypoint hour
Input TIMESTAMP date optionally time 
Output INTEGER hours of the timestamp
If you entered only date, the hour is = 0.
TestSQL
SELECT 14 AS ISCORRECT, F_HOUR('23.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_HOUR(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_MINUTE compatibility to GrUDF
input/output-compatibility to rFunc (EXTRACTMINUTE)
ersetzbar durch EXTRACT(MINUTE FROM ...)
Entrypoint minute
Input TIMESTAMP date optionally time 
Output INTEGER minutes of the timestamp
If you entered only date, the minute is = 0.
TestSQL
SELECT 38 AS ISCORRECT, F_MINUTE('23.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MINUTE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_SECOND compatibility to zu GrUDF
input/output-compatibility to rFunc (EXTRACTSECOND)
ersetzbar durch EXTRACT(SECOND FROM ...)
Entrypoint second
Input TIMESTAMP date optionally time 
Output INTEGER seconds of the timestamp
If you entered only date, the second is = 0.
TestSQL
SELECT 12 AS ISCORRECT, F_SECOND('23.08.2004 14:38:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_SECOND(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_MILLISECOND input/output-compatibility to rFunc (EXTRACTMILLISECOND)
Entrypoint millisecond
Input TIMESTAMP date optionally time 
Output INTEGER milliseconds of the timestamp
If you entered only date, the second is = 0.
TestSQL
SELECT 3456 AS ISCORRECT, F_MILLISECOND('23.08.2004 14:38:12 3456') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MILLISECOND(NULL) FROM RDB$DATABASE;
nach oben • go top •
 
Date-time functions: Format
F_CMONTHLONG compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint cmonthlong
Input TIMESTAMP date optionally time 
Output CSTRING(16) month in english
TestSQL
SELECT 'August' AS ISCORRECT, F_CMONTHLONG('23.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CMONTHLONG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CMONTHLONGLANG function from adhoc
Entrypoint cmonthlonglang
Input TIMESTAMP
CSTRING(2)
date optionally time 
language identifier for the output
Output CSTRING(16) month in chosen language
Language version of  F_CMONTHLONG.
Language identifier: de = German, uk = English, fr = French, es = Spanish, it =Italian, es = Spanish, pt = Portuguese,
nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic),
ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots,
s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese,
lu = Luxembourgish, w2 = Wallon
TestSQL
SELECT 'Août' AS ISCORRECT, F_CMONTHLONGLANG('23.08.2004', 'fr') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CMONTHLONGLANG(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CMONTHSHORT compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint cmonthshort
Input TIMESTAMP date optionally time 
Output CSTRING(4) shortcut month in english
TestSQL
SELECT 'Aug' AS ISCORRECT, F_CMONTHSHORT('23.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CMONTHSHORT(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CMONTHSHORTLANG function from adhoc
Entrypoint cmonthshortlang
Input TIMESTAMP
CSTRING(2)
date optionally time 
language identifier for the output
Output CSTRING(4) shortcut month in chosen language
Language version of  F_CMONTHSHORT.
Language identifier: de = German, uk = English, fr = French, es = Spanish, it =Italian, es = Spanish, pt = Portuguese,
nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic),
ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots,
s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese,
lu = Luxembourgish, w2 = Wallon
TestSQL
SELECT 'Aoû' AS ISCORRECT, F_CMONTHSHORTLANG('23.08.2004', 'fr') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CMONTHSHORTLANG(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CDOWLONG compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint cdowlong
Input TIMESTAMP date optionally time 
Output CSTRING(16) day of the week in english
TestSQL
SELECT 'Monday' AS ISCORRECT, F_CDOWLONG('23.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CDOWLONG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CDOWLONGLANG function from adhoc
Entrypoint cdowlonglang
Input TIMESTAMP
CSTRING(2)
date optionally time
language identifier for the output
Output CSTRING(16) day of the week in chosen language
Language version of  F_CDOWLONG.
Language identifier: de = German, uk = English, fr = French, es = Spanish, it =Italian, es = Spanish, pt = Portuguese,
nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic),
ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots,
s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese,
lu = Luxembourgish, w2 = Wallon
TestSQL
SELECT 'Lundi' AS ISCORRECT, F_CDOWLONGLANG('23.08.2004', 'fr') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CDOWLONGLANG(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CDOWSHORT compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP, GrUDF
Entrypoint cdowshort
Input TIMESTAMP date optionally time 
Output CSTRING(4) shortcut day of week in english
TestSQL
SELECT 'Mon' AS ISCORRECT, F_CDOWSHORT('23.08.2004') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CDOWSHORT(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_CDOWSHORTLANG function from adhoc
Entrypoint cdowshortlang
Input TIMESTAMP
CSTRING(2)
date optionally time 
language identifier for the output
Output CSTRING(16) shortcut day of the week in chosen language
Language version of  F_CDOWSHORT.
Language identifier: de = German, uk = English, fr = French, es = Spanish, it =Italian, es = Spanish, pt = Portuguese,
nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic),
ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots,
s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese,
lu = Luxembourgish, w2 = Wallon
TestSQL
SELECT 'Lun' AS ISCORRECT, F_CDOWSHORTLANG('23.08.2004', 'fr') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_CDOWSHORTLANG(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_GFORMATD function from adhoc
Entrypoint gformatd
Input CSTRING(254)
TIMESTAMP
pattern of timestamp
date optionally time
Output CSTRING(254) date formated like pattern from parameter 1
Parameter 1
        d      = day possibly one-digit
        dd    = day always in two digits
        m     = month possibly one-digit
        mm  = month always in two-digits
        yy    = year always in two digits
        yyyy = year always in four digits
        h      = hour possibly one-digit
        hh    = hour always in two digits
        n     = minute possibly one-digit
        nn   = minute always in two digits
        s     = second possibly one-digit
        ss    = second always in two digits
        all other indications will indicate according to their task in parameters 1 in the respective place.
TestSQL
SELECT '01-10-2005 15:09:12' AS ISCORRECT, F_GFORMATD('dd-mm-yyyy hh:nn:ss', '01.10.2005 15:09:12') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_GFORMATD(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DATETOSTR input/output-compatibility to rFunc (DATETOSTR)
Entrypoint datetostr
Input TIMESTAMP
CSTRING(254)
date optionally time
pattern
Output CSTRING(254) date formated with parameter 2
Pattern for parameter 2 (similar to c-function strftime):
     %% - character %,
     %c - date and time,
     %x - date,
     %d - day of a month (01-31),
     %j - day of year (000-366),
     %w - day of week (0-6), 0 - Sunday,
     %U - number of week in one year (00-53), Sunday - the first day of week,
     %W - number of week in one year (00-53), Monday - the first day of week,
     %a - the short name of day of week,
     %A - the full name of day of week,
     %m - month (01-12),
     %b - the short name of day of month,
     %B - the full name of day of month,
     %y - year (00-99),    %Y - four-digit year,
     %X - time,
     %H - hour (00-23),   %I - hour (01-12),
     %M - minute (00-59),
     %p - AM or PM,
     %S - second (00-59).
TestSQL
SELECT '10 February 2004, 08:23:00' AS ISCORRECT, F_DATETOSTR('10.02.04 08:23', '%d %B %Y, %X') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DATETOSTR(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DATETOSTRLANG function from adhoc
Entrypoint datetostrlang
Input TIMESTAMP
CSTRING(254)
CSTRING(2)
date optionally time
pattern
language identifier for the output
Output CSTRING(254) date formated with parameter 2 in choosen language
Language version of  F_DATETOSTR.
Language identifier: de = German, uk = English, fr = French, es = Spanish, it =Italian, es = Spanish, pt = Portuguese,
nl = Dutch, no = Norwegian, Bokmål, se = Swedish, dk = Danish, fi = Finnish, hu = Hungarian, ie = Irish(Gaelic),
ee = Estonian, is = Icelandic, al = Albanian, va = Classical Latin, v1 = Ecclesiastical Latin, c1 = Catalan, s1 = Scots,
s2 = Scottish Gaelic, w1 = Welsh, b1 = Breton, b2 = Basque, n1 = Norwegian, Nynorsk, za = Afrikaans, fo = Faroese,
lu = Luxembourgish, w2 = Wallon
Pattern for parameter 2 (similar to c-function strftime): sea F_DATETOSTR
TestSQL
SELECT 'Tuesday, 10 February 2004, 08:23:00' AS ISCORRECT, F_DATETOSTRLANG('10.02.04 08:23', '%A, %d %B %Y, %X', 'uk') FROM RDB$DATABASE;
SELECT 'Dienstag, 10 Februar 2004, 08:23:00' AS ISCORRECT, F_DATETOSTRLANG('10.02.04 08:23', '%A, %d %B %Y, %X', 'de') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DATETOSTRLANG(NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ENCODEDATE compatibility to GrUDF
Entrypoint encodedate
Input INTEGER
INTEGER
INTEGER
year
month
day
Output DATE date built from parameters
TestSQL
SELECT '20.02.2004' AS ISCORRECT, F_ENCODEDATE(2004, 2, 20) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ENCODEDATE(NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_RENCODEDATE input/output-compatibility to rFunc (ENCODEDATE)
Entrypoint r_encodedate
Input INTEGER
INTEGER
INTEGER
day
month
year
Output DATE date build from parameters
Input-parameter in other order than F_ENCODEDATE
TestSQL
SELECT '20.02.2004' AS ISCORRECT, F_RENCODEDATE(20, 2, 2004) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_RENCODEDATE(NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ENCODETIME compatibility to zu GrUDF
Entrypoint encodetime
Input INTEGER
INTEGER
INTEGER
hours
minutes
seconds
Output TIME time build from parameters
TestSQL
SELECT '09:45:53' AS ISCORRECT, F_ENCODETIME(9, 45, 53) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ENCODETIME(NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ENCODETIMESTAMP compatibility to GrUDF
Entrypoint encodetimestamp
Input INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
year
month
day
hours
minutes
seconds
Output TIMESTAMP timestamp build from parameters
TestSQL
SELECT '20.02.2004 09:45:53' AS ISCORRECT, F_ENCODETIMESTAMP(2004, 2, 20, 9, 45, 53) FROM RDB$DATABASE;
SELECT '20.02.2004 00:00:00' AS ISCORRECT, F_ENCODETIMESTAMP(2004, 2, 20, 0, 0, 0) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ENCODETIMESTAMP(NULL, NULL, NULL, NULL, NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_RENCODEDATETIME input/output-compatibility to rFunc (ENCODEDATETIME)
Entrypoint r_encodedatetime
Input INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
day
month
year
hours
minutes
seconds
Output TIMESTAMP timestamp build from parameters
Input-parameter in other order than F_ENCODETIMESTAMP
TestSQL
SELECT '20.02.2004 09:45:53' AS ISCORRECT, F_RENCODEDATETIME(20, 2, 2004, 9, 45, 53) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_RENCODEDATETIME(NULL, NULL, NULL, NULL, NULL, NULL)) FROM RDB$DATABASE;
nach oben • go top •
F_STRIPDATE compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
input/output-compatibility to rFunc (EXTRACTTIME)
Entrypoint stripdate
Input TIMESTAMP date optionally time
Output TIMESTAMP timestamp with date 31.12.1899 (date 0) with chosen time
To get only the time use CAST(' 01.10.2005 15:00:00' AS TIME)
TestSQL
SELECT '31.12.1899 15:00:00' AS ISCORRECT, F_STRIPDATE(' 01.10.2005 15:00:00')
FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_STRIPDATE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_STRIPTIME compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
input/output-compatibility to rFunc (EXTRACTDATE)
Entrypoint striptime
Input TIMESTAMP date optionally time
Output TIMESTAMP timestamp with time 00:00:00 from chosen date
To get only the date use CAST(‘01.10.2005 15:00:00' AS DATE)
TestSQL
SELECT '01.10.2005 00:00:00' AS ISCORRECT, F_STRIPTIME(' 01.10.2005 15:00:00')
FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_STRIPTIME(NULL) FROM RDB$DATABASE;
nach oben • go top •
 
Date-time functionsCompare
F_MAXDATE compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
input/output-compatibility to rFunc (MAXDATE)
Entrypoint maxdate
Input TIMESTAMP
TIMESTAMP
date optionally time 1
date optionally time 2
Output TIMESTAMP the newer date
TestSQL
SELECT '01.10.2005 15:00:00' AS ISCORRECT, F_MAXDATE('22.08.2000 14:38:12', '01.10.2005 15:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MAXDATE(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_MINDATE compatibility to FreeUDFLib, FreeUDFLibC, FreeUDFLib AvERP
input/output-compatibility to rFunc (MINDATE)
Entrypoint mindate
Input TIMESTAMP
TIMESTAMP
date optionally time 1
date optionally time 2
Output TIMESTAMP the older date
TestSQL
SELECT '22.08.2000 14:38:12' AS ISCORRECT, F_MINDATE('22.08.2000 14:38:12', '01.10.2005 15:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_MINDATE(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_EQUALDATE compatibility to zu FreeUDFLib AvERP, GrUDF
Entrypoint equaldate
Input TIMESTAMP
TIMESTAMP
date optionally time 1
date optionally time 2
Output INTEGER 1 = date is equal, 0 = date is not equal
Tested only on date (time could be entered but never used)
TestSQL
SELECT 1 AS ISCORRECT, F_EQUALDATE('20.02.2004 10:00:00', '20.02.2004 11:00:00') FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_EQUALDATE('20.02.2004 10:00:00', '21.02.2004 11:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_EQUALDATE(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
F_EQUALDATETIME compatibility to FreeUDFLib AvERP, GrUDF
input/output-compatibility to rFunc (DTEQUAL)
Entrypoint equaldatetime
Input TIMESTAMP
TIMESTAMP
date optionally time 1
date optionally time 2
Output INTEGER 1 =timestamp is equal, 0 = timestamp is not equal
Testet on date and time
TestSQL
SELECT 1 AS ISCORRECT, F_EQUALDATETIME('20.02.2004 10:00:00', '20.02.2004 10:00:00') FROM RDB$DATABASE;
SELECT 0 AS ISCORRECT, F_EQUALDATETIME('20.02.2004 10:00:00', '20.02.2004 11:00:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_EQUALDATETIME(NULL, NULL) FROM RDB$DATABASE;
nach oben • go top •
 
Date-time functionsCovert
F_STRTOTIME compatibility to FreeUDFLibC
Entrypoint strtotime
Input CSTRING(11) british/american time (f.e. 05:04:01 AM) as string
Output TIME time in format 24 hours
Inputformat could be with one or two digits and the separation from AM/PM with or without blanks, AM/PM must be entered in capital letters.
TestSQL   
SELECT '05:04:01' AS ISCORRECT, F_STRTOTIME('05:04:01 AM') FROM RDB$DATABASE;
SELECT '17:04:01' AS ISCORRECT, F_STRTOTIME('5:4:1PM') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_STRTOTIME(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DATETODOUBLE input/output-compatibility to rFunc (DATETODOUBLE)
Entrypoint datetodouble
Input TIMESTAMP date optionally time
Output DOUBLE timestamp as  floatingpoint (infront of dot date, after dot time; 1.0 = 24 h)
TestSQL
SELECT 54205.59375 AS ISCORRECT, F_DATETODOUBLE('15.04.2007 14:15:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DATETODOUBLE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_TIMETODOUBLE input/output-compatibility to rFunc (TIMETODOUBLE)
Entrypoint timetodouble
Input TIMESTAMP date optionally time
Output DOUBLE timestamp as  floatingpoint (infront of dot 0, after dot time; 1.0 = 24 h)
TestSQL
SELECT 0.59375 AS ISCORRECT, F_TIMETODOUBLE('15.04.2007 14:15:00') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_TIMETODOUBLE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DOUBLETODATE input/output-compatibility to rFunc (DOUBLETODATE)
Entrypoint doubletodate
Input DOUBLE timestamp as  floatingpoint (infront of dot date, after dot time; 1.0 = 24 h)
Output TIMESTAMP timestamp
rFunc uses modified julian calendar (0 = 17.11.1858 00:00:00) Because of this with date 0 returns 31.12.1899 instead of 17.11.1858.
TestSQL
SELECT '15.04.2007 14:15:00' AS ISCORRECT, F_DOUBLETODATE(54205.59375) FROM RDB$DATABASE;
SELECT NULL' AS ISCORRECT, F_DOUBLETODATE(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_DOUBLETOTIME input/output-compatibility to rFunc (DOUBLETOTIME)
Entrypoint doubletotime
Input DOUBLE timestamp as  floatingpoint (infront of dot date, after dot time; 1.0 = 24 h)
Output TIMESTAMP timestamp
Uses from input only digits after dot (the time-part).
rFunc uses modified julian calendar (0 = 17.11.1858 00:00:00) Because of this with date 0 returns 31.12.1899 instead of 17.11.1858.
TestSQL
SELECT '31.12.1899 14:15:00' AS ISCORRECT, F_DOUBLETOTIME(0.59375) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_DOUBLETOTIME(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_PERIOD2SECONDS function from adhoc
Entrypoint period2seconds
Input CSTRING(254) period in pattern [d]:h:m:s
Output INTEGER period in seconds
Periods could be enter with 2- or 1digits interval (dd:hh:mm:ss or d:h:m:s).
Negative periods starts with a "-" in front.
Periods with pattern days:hours:minutes:seconds (4 intervalls) or hours:minutes:seconds (3 intervalls) are allowed.
The output of periods with less than 3 intervalls or containing other characters is <null> or empty string.
For input also f.e. '0:26:0:0' instead of '1:2:0:0' alllowed for 26 hours.
TestSQL
SELECT 4205 AS ISCORRECT, F_PERIOD2SECONDS('0:1:10:5') FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_PERIOD2SECONDS(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_SECONDS2PERIOD function from adhoc
Entrypoint seconds2period
Input INTEGER seconds of a period
Output CSTRING(254) period in pattern d:h:m:s
The output is allways in pattern days:hours:minutes:seconds (4 * 2 digit intervalls).
Negativ periods starts with "-".
TestSQL
SELECT '00:01:10:05' AS ISCORRECT, F_SECONDS2PERIOD(4205) FROM RDB$DATABASE;
SELECT '-00:01:10:05' AS ISCORRECT, F_SECONDS2PERIOD(-4205) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_SECONDS2PERIOD(NULL) FROM RDB$DATABASE;
nach oben • go top •
 
Date-time functions: movable feasts
F_WEIBERFASTNACHT function from adhoc
Entrypoint weiberfastnacht
Input INTEGER year
Output DATE date of Carnival Dirty Thuresday of the year
TestSQL
SELECT '15.02.2007' AS ISCORRECT, F_WEIBERFASTNACHT(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_WEIBERFASTNACHT(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ROSENMONTAG function from adhoc
Entrypoint rosenmontag
Input INTEGER year
Output DATE date of Carnival Monday of the year
TestSQL
SELECT '19.02.2007' AS ISCORRECT, F_ROSENMONTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ROSENMONTA(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_KARNEVALSDIENSTAG function from adhoc
Entrypoint karnevalsdienstag
Input INTEGER year
Output DATE date of Mardi Gras of the year
TestSQL
SELECT '20.02.2007' AS ISCORRECT, F_KARNEVALSDIENSTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_KARNEVALSDIENSTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_ASCHERMITTWOCH function from adhoc
Entrypoint aschermittwoch
Input INTEGER year
Output DATE date of Ash Wednesday of the year
TestSQL
SELECT '21.02.2007' AS ISCORRECT, F_ASCHERMITTWOCH(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_ASCHERMITTWOCH(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_PALMSONNTAG function from adhoc
Entrypoint palmsonntag
Input INTEGER year
Output DATE date of Palm Sunday of the year
TestSQL
SELECT '01.04.2007' AS ISCORRECT, F_PALMSONNTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_PALMSONNTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_GRUENDONNERSTAG function from adhoc
Entrypoint gruendonnerstag
Input INTEGER year
Output DATE date of Holy Thursday of the year
TestSQL
SELECT '05.04.2007' AS ISCORRECT, F_GRUENDONNERSTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_GRUENDONNERSTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_KARFREITAG function from adhoc
Entrypoint karfreitag
Input INTEGER year
Output DATE date of Easter Friday of the year
TestSQL
SELECT '06.04.2007' AS ISCORRECT, F_KARFREITAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_KARFREITAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_OSTERSAMSTAG function from adhoc
Entrypoint ostersamstag
Input INTEGER year
Output DATE date of Easter Saturday of the year
TestSQL
SELECT '07.04.2007' AS ISCORRECT, F_OSTERSAMSTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_OSTERSAMSTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_OSTERDATUM compatibility to FreeUDFLib AvERP, GrUDF
Entrypoint osterdatum
Input INTEGER year
Output DATE date of Easter Sunday of the year
TestSQL
SELECT '27.03.2005' AS ISCORRECT, F_OSTERDATUM(2005) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_OSTERDATUM(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_OSTERMONTAG function from adhoc
Entrypoint ostermontag
Input INTEGER year
Output DATE date of Easter Monday of the year
TestSQL
SELECT '09.04.2007' AS ISCORRECT, F_OSTERMONTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_OSTERMONTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_HIMMELFAHRT function from adhoc
Entrypoint himmelfahrt
Input INTEGER year
Output DATE date of Ascension of Chris of the year
TestSQL
SELECT '17.05.2007' AS ISCORRECT, F_HIMMELFAHRT(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_HIMMELFAHRT(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_PFINGSTSONNTAG function from adhoc
Entrypoint pfingstsonntag
Input INTEGER year
Output DATE date of Whitsunday of the year
TestSQL
SELECT '27.05.2007' AS ISCORRECT, F_PFINGSTSONNTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_PFINGSTSONNTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_PFINGSTMONTAG function from adhoc
Entrypoint pfingstmontag
Input INTEGER year
Output DATE date of Whitmonday of the year
TestSQL
SELECT '28.05.2007' AS ISCORRECT, F_PFINGSTMONTAG(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_PFINGSTMONTAG(NULL) FROM RDB$DATABASE;
nach oben • go top •
F_FRONLEICHNAM function from adhoc
Entrypoint fronleichnam
Input INTEGER year
Output DATE date of Feast of Corpus Christi of the year
TestSQL
SELECT '07.06.2007' AS ISCORRECT, F_FRONLEICHNAM(2007) FROM RDB$DATABASE;
SELECT NULL AS ISCORRECT, F_FRONLEICHNAM(NULL) FROM RDB$DATABASE;
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site