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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |
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; |
  |