Date Functions
|
Previous Top Next |
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
|
CURRENT_DATE returns 01/01/2003 (for example)
|
|
|
CURRENT_TIME returns 10:25:01 AM (for example)
|
|
|
CURRENT_TIMESTAMP returns 01/01/2003 10:25:01 AM (for example)
|
|
|
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 '-'.
|
TOSTRING(NOW,'mm/dd/yyyy') returns 01/01/2003 (for example)
|
|
|
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 '-'.
|
TODATE('01/01/2003','mm/dd/yyyy') returns 01/01/2003
|
|
|
|
date_part
|
Range
|
YEAR
|
1 to 9999
|
MONTH
|
1 to 12
|
DAY
|
1 to 31
|
HOUR
|
0 to 23
|
MINUTE
|
0 to 59
|
SECOND
|
0 to 59
|
EXTRACT (YEAR FROM date_field) returns 2003 (for example if date_field value is 01/01/2003)
|
|
|
|
|