Date Functions

<< Click to Display Table of Contents >>

Navigation:  SQL Reference > Functions and Operators >

Date Functions

Previous pageReturn to chapter overviewNext page

 

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

 

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

1 to 9999

MONTH

1 to 12

DAY

1 to 31

HOUR

0 to 23

MINUTE

0 to 59

SECOND

0 to 59

Example:

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

__________________________
Absolute SQL Reference - 25 May 2022, Niels Knabe