Date and time functions

CURRENT_TIME

Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

CURRENT_DATE

Returns the current date in the session time zone, in a value of datatype DATE

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

YEAR(date)

Extracts and returns the value of the year from a datetime value expression. Returns an integer.

SELECT YEAR(TIMESTAMP '1971-07-20 09:34:21') :  1971

SELECT YEAR(DATE '1968-07-20') :  1968

SELECT YEAR(hiringDate) FROM employee WHERE name='Smith' :  2015

SELECT YEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  2017

QUARTER(date)

Extracts and returns the value of the quarter from a datetime value expression. Returns an integer between 1 and 4.

SELECT QUARTER(TIMESTAMP '1971-07-20 09:34:21') :  3

SELECT QUARTER(hiringDate) FROM employee WHERE name='Smith' :  4

SELECT QUARTER(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  2

QUARTER(date)

Extracts and returns the value of the month from a datetime value expression. Returns an integer between 1 and 12.

SELECT MONTH(TIMESTAMP '1971-07-20 09:34:21') :  7

SELECT MONTH(hiringDate) FROM employee WHERE name='Smith' :  10

SELECT MONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  6

WEEK(date)

Extracts and returns the value of the week from a datetime value expression. Returns an integer between 1 and 53.

SELECT WEEK(TIMESTAMP '1971-07-20 09:34:21') :  29

SELECT WEEK(hiringDate) FROM employee WHERE name='Smith' :  42

SELECT WEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  26

DAYOFYEAR(date)

Extracts and returns the value of the day of year from a datetime value expression. Returns an integer between 1 and 366.

SELECT DAYOFYEAR(TIMESTAMP '1971-07-20 09:34:21') :  201

SELECT DAYOFYEAR(hiringDate) FROM employee WHERE name='Smith' :  287

SELECT DAYOFYEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  179

DAYOFMONTH(date)

Extracts and returns the value of the day of month from a datetime value expression. Returns an integer between 1 and 31.

SELECT DAYOFMONTH(TIMESTAMP '1971-07-20 09:34:21') :  20

SELECT DAYOFMONTH(hiringDate) FROM employee WHERE name='Smith' :  14

SELECT DAYOFMONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  28

DAYOFWEEK(date)

Extracts and returns the value of the day of week from a datetime value expression. Returns an integer between 1 and 7.

SELECT DAYOFWEEK(TIMESTAMP '1971-07-20 09:34:21') :  3

SELECT DAYOFWEEK(hiringDate) FROM employee WHERE name='Smith' :  4

SELECT DAYOFWEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  4

HOUR(date)

Extracts and returns the value of the hours from a datetime value expression. Returns an integer between 0 and 23.

SELECT HOUR(TIMESTAMP '1971-07-20 09:34:21') :  9

SELECT HOUR(TIME '10:34:21') :  10

SELECT HOUR(workStart) FROM employee WHERE name='Smith' :  8

SELECT HOUR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  16

MINUTE(date)

Extracts and returns the value of the minutes from a datetime value expression. Returns an integer between 0 and 59.

SELECT MINUTE(TIMESTAMP '1971-07-20 09:34:21') :  34

SELECT MINUTE(TIME '10:35:21') :  35

SELECT MINUTE(workStart) FROM employee WHERE name='Smith' :  0

SELECT MINUTE(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  6

SECOND(date)

Extracts and returns the value of the seconds from a datetime value expression. Returns an integer between 0 and 59.

SELECT HOUR(TIMESTAMP '1969-07-20 09:34:21') :  9

SELECT SECOND(TIME '10:34:22') :  22

SELECT SECOND(workStart) FROM employee WHERE name='Smith' :  0

SELECT SECOND(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  12