SQL Reference Absolute Database > SQL Date functions

Date Functions

Absolute Database supports the following Date/Time functions:

Function	Description
CURRENT_DATE	Returns the current date
CURRENT_TIME	Returns the current time
CURRENT_TIMESTAMP	Returns the current local date and local time as a timestamp value
NOW	Returns current date and time as a timestamp value
SYSDATE	Returns current date and time as a timestamp value
TOSTRING	Converts date and time to string
TODATE	Converts string to date and time
EXTRACT	Extracts datepart component from datetime value as INTEGER

Note:
CURRENT_DATE, CURRENT_TIME og CURRENT_TIMESTAMP er ikke ISO-SQL men anvendes dog foruden hér også i bl.a. Oracle's databaser, der globalt er en af de mest udbredte databaser.
TOSTRING og TODATE er proprietære SQL-navne for datofunktioner, der i.eks. Oracle databaser hedder TO_CHAR og TO_DATE.
Den ISO certificerede EXTRACT må foretrækkes fremfor den proprietære TOSTRING ved udtræk af dato-tids-værdier.

CURRENT_DATE

Returns the current date.

Example:

CURRENT_DATE returns 01/01/2003 (for example)

CURRENT_TIME

Returns the current time.

Example:

CURRENT_TIME returns 10:25:01 AM (for example)

CURRENT_TIMESTAMP, NOW, SYSDATE

Returns the current local date and local time as a timestamp value.

Example:

CURRENT_TIMESTAMP returns 01/01/2003 10:25:01 AM (for example)

TOSTRING (datetime_expr, date_format )

Converts date and/or time expression in datetime_expr to string in accordance with date_format format string.

The following is a list of options for the date_format parameter. These parameters can be used in many combinations.

Parameter	Description
D	Displays the day as a number without a leading zero (1-31).
DD	Displays the day as a number with a leading zero (01-31).
DDD	Displays the day as an abbreviation (Sun-Sat).
DAY	Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames global variable.
DY	Day of year (1-366).
DW	Day of week (1-7).
M	Displays the month as a number without a leading zero (1-12).
MM	Displays the month as a number with a leading zero (01-12).
MON	Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames global variable.
MONTH	Displays the month as a full name (January-December) using the strings given by the LongMonthNames global variable.
RM	Roman numeral month (I-XII; JAN = I).
YY	Displays the year as a two-digit number (00-99).
YYYY, YEAR	Displays the year as a four-digit number (0000-9999).
H, H12	Hour of day without a leading zero (1-12).
H24	Hour of day without a leading zero (1-24).
HH, HH12	Hour of day with a leading zero (01-12).
HH24	Hour of day with a leading zero (01-24).
N	Displays the minute without a leading zero (0-59).
NN	Displays the minute with a leading zero (00-59).
S	Displays the second without a leading zero (0-59).
SS	Displays the second with a leading zero (00-59).
AMPM	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'AM' for any hour before noon, and 'PM' for any hour after noon.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
'Text', "Text"	Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.
:	Displays the time separator character ':'.
/	Displays the date separator character '/'.
,	Displays the character ','.
.	Displays the character '.'.
;	Displays the character ';'.
-	Displays the character '-'.

Example:

TOSTRING(NOW,'mm/dd/yyyy') returns 01/01/2003 (for example)

TODATE (string_expr, date_format )

Converts string in string_expr to to date and/or time expression in accordance with date_format format string.

The following is a list of options for the date_format parameter. These parameters can be used in many combinations.

Parameter	Description
D	Displays the day as a number without a leading zero (1-31).
DD	Displays the day as a number with a leading zero (01-31).
M	Displays the month as a number without a leading zero (1-12).
MM	Displays the month as a number with a leading zero (01-12).
YY	Displays the year as a two-digit number (00-99).
YYYY, YEAR	Displays the year as a four-digit number (0000-9999).
H, H12	Hour of day without a leading zero (1-12).
H24	Hour of day without a leading zero (1-24).
HH, HH12	Hour of day with a leading zero (01-12).
HH24	Hour of day with a leading zero (01-24).
N	Displays the minute without a leading zero (0-59).
NN	Displays the minute with a leading zero (00-59).
S	Displays the second without a leading zero (0-59).
SS	Displays the second with a leading zero (00-59).
AMPM	Uses the 12-hour clock for the preceding h or hh specifier, and displays 'AM' for any hour before noon, and 'PM' for any hour after noon.
'Text', "Text"	Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.
:	Displays the time separator character ':'.
/	Displays the date separator character '/'.
,	Displays the character ','.
.	Displays the character '.'.
;	Displays the character ';'.
-	Displays the character '-'.

Example:

TODATE('01/01/2003','mm/dd/yyyy') returns 01/01/2003

EXTRACT (date_part FROM datetime_expression)

Extracts datepart component from datetime value as INTEGER.

date_part could be one of the following expressions:

date_part

Range

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

1 to 9999

1 to 12

1 to 31

0 to 23

0 to 59

Example:

EXTRACT (YEAR FROM date_field) returns 2003 (for example if date_field value is 01/01/2003)

_____________________________

27-10-2017 - Niels Knabe