Date and Time Functions

4. Understanding the Elements of SQL Statements : SQL Functions : Scalar Functions : Date and Time Functions

Share this page

Date and time functions operate on a date and time input value and return a string, numeric, or date and time value.

DATE_FORMAT

DATE_FORMAT(datetime, format)

TIME_FORMAT

TIME_FORMAT(datetime, format)

TIME_FORMAT is an alias for DATE_FORMAT.

Operand type: datetime is a DATE, TIME, or TIMESTAMP; format is a character string

Result type: VARCHAR

Returns datetime formatted according to the format string.

The specifiers in the following table can be used in the format string. The “%” character is required before format specifier characters. If the format specifier is inappropriate to the data type of datetime, then NULL is returned.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (1st, 2nd, 3rd, …)
%d	Day of the month, numeric (01‑31)
%e	Day of the month, numeric (1‑31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric (four digits)
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any "x" not listed above

DATE_FORMAT('2010-10-03 22:23:00', '%W %M %Y')

returns 'Sunday October 2010'

DATE_FORMAT('2007-10-03 22:23:00', '%H:%i:%s')

returns '22:23:00'

DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')

returns '4th 00 Thu 04 10 Oct 277'

DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w')

returns '22 22 10 10:23:00 PM 22:23:00 00 6'

DATE_FORMAT('1999-01-01', '%X %V')

returns '1998 52'

DATE_GMT

DATE_GMT(datetime)

Result type: any character data type

Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.

For example, the query:

SELECT DATE_GMT('1-1-98 10:13 PM PST')

returns:

1998_01_02 06:13:00 GMT

while the query:

SELECT DATE_GMT('1-1-1998')

returns:

1998_01_01 00:00:00 GMT

DATE_PART

DATE_PART(unit,date)

Result type: INTEGER

Returns an integer containing the specified unit component of the input date.

The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter is case sensitive.

Date Portion	How Unit Parameter Is Specified
Year	YEAR, YEARS, YR, YRS
Quarter	QUARTER, QUARTERS, QTR, QTRS
Month	MONTH, MONTHS, MO, MOS
Week	WEEK, WEEKS, WK, WKS
ISO-Week	ISO-WEEK, ISO-WK
Day of month	DAY, DAYS
Day of week	DAYOFWEEK, DOW
Day of year	DAYOFYEAR, DOY
Hour	HOUR, HOURS, HR, HRS
Minute	MINUTE, MINUTES, MIN, MINS
Second	SECOND, SECONDS, SES, SECS
Milliseconds	MILLISECOND
Microseconds	MICROSECOND
Nanoseconds	NANOSECOND
UNIX timestamp	EPOCH

Many of these units can also be derived using the EXTRACT function or using explicit, individual extract functions such as HOUR() or MILLISECOND().

The DATE_PART function is useful in set functions and in ensuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-2012, then:

DATE_PART('MONTH',DATE(date_field))

returns a value of 10 (representing October), and

DATE_PART('DAY',DATE(date_field))

returns a value of 23.

Months are numbered 1 to 12, starting with January. Hours are returned according to the 24-hour clock. Quarters are numbered 1 through 4.

Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.

Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and DATE_PART returns either 52 or 53.

The following table illustrates the difference between Week and ISO-Week:

Date Column	Day of Week	Week	ISO-Week
02-jan-2009	Fri	0	1
04-jan-2009	Sun	0	1
02-jan-2010	Sat	0	53
04-jan-2010	Mon	1	1
02-jan-2011	Sun	0	52
04-jan-2011	Tue	1	1
02-jan-2012	Tue	1	1
04-jan-2012	Thu	1	1

DATE_TRUNC

DATE_TRUNC(unit,date)

Operand type: date can be absolute INGRESDATE, ANSIDATE, TIME, TIMESTAMP

Result type: Same as operand type

Returns a date value truncated to the specified unit. Valid unit parameters are described under DATE_PART (see DATE_PART).

Where unit is DAY or greater, the day boundary is taken to be in the user's time zone. For example, the following query against a timestamp with time zone value:

SELECT DATE_TRUNC('DAY',TIMESTAMP '2011-02-03 16:12:13.000000000-08:00')

returns

2011-02-03 00:00:00.000000000-08:00

which retains the original time zone offset.

Use the DATE_TRUNC function to group all the dates within the same month or year, and so forth. For example:

DATE_TRUNC('MONTH',DATE('23-oct-1998 12:33'))

returns 1-oct-1998, and

DATE_TRUNC('YEAR',DATE('23-oct-1998'))

returns 1-jan-1998.

Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).

To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):

DATE_TRUNC('YEAR',date+'4 mos') - '4 mos'

Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.

DAY

DAY('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the day portion of a date or timestamp.

DAY(TIMESTAMP '2006-12-15 12:30:55.1234') returns 15

DAYOFMONTH

DAYOFMONTH(datetime)

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER.

Returns the day of the month from the specified datetime value.

DAYOFMONTH(DATE '2011-02-15') returns 15

DAYOFWEEK

DAYOFWEEK(datetime [,n])

Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.

Result type: INTEGER

Returns the day of the week from the specified datetime value, where 1 = Sunday unless n is used to shift the start of week.

DAYOFWEEK(DATE '2011-02-15',4) returns 6

DAYOFWEEK(DATE '2011-02-15') returns 3

DAYOFYEAR

DAYOFYEAR(datetime)

Returns the ordinal number of day in the year, in the range 1 to 366, for datetime.

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER

DAYOFYEAR(DATE '2011-02-04') returns 35

DOY

Same as DAYOFYEAR.

DOW

DOW(datetime)

Operand type: datetime is a DATE or TIMESTAMP

Returns the day of the week as a 3‑character string.

DOW('2011-12-25') returns Sun.

EXTRACT

EXTRACT (part FROM datetime)

Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.

Result type: INTEGER

Extracts a particular field from a date/time value. Part specifies the field to extract.

A part parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.

Valid values for part are:

YEAR

Year field. Range: 0 - 9999

MONTH

Month field. Range: 1 - 12

DAY

Day field. Range: 1 - 31

HOUR

Hour field. Range: 0 - 23

MINUTE

Minute field. Range: 0 - 59

SECOND

Second field. Range: 0 - 59

MILLISECONDS

Fractional seconds as milliseconds. Range: 0 - 999.

MICROSECONDS

Fractional seconds as microseconds. Range: 0 - 999.

NANOSECONDS

Fractional seconds as nanoseconds. Range: 0 - 999.

TIMEZONE_HOUR

Time zone hour offset. Range: ‑12 ‑ 14.

TIMEZONE_MINUTE

Time zone minute offset. Range: 0 - 59

DAYOFWEEK

Day of week with Sunday=1. Range: 1 - 7.

DOW

Day of week with Sunday=1. Range: 1 - 7.

DAYOFYEAR

Day of year. Range: 1 - 366.

DOY

Day of year. Range: 1 - 366.

WEEK

Week of year. Range: 1 - 53.

ISO_WEEK

Week of year ISO 6801.

QUARTER

Year quarter. Range: 1 - 4.

EPOCH

UNIX timestamp (number of seconds since 1-Jan-1970).

Note: The datetime value cannot be an interval type on the TIMEZONE_HOUR, TIMEZONE_MINUTE, DAYOFWEEK, DAYOFYEAR, WEEK, ISO_WEEK, QUARTER, and EPOCH functions.

Examples:

SELECT EXTRACT (YEAR FROM datecol) FROM datetable;

SELECT EXTRACT (MONTH FROM CURRENT_DATE);

SELECT EXTRACT (HOUR FROM timecol) FROM datetable;

SELECT * FROM tx WHERE EXTRACT(HOUR FROM datetable) = 17;

FROM_UNIXTIME

Operand types: INTEGER, character string

Formats UNIX timestamp as a date.

FROM_UNIXTIME

FROM_UNIXTIME(i)

Result type: TIMESTAMP WITHOUT TIME ZONE

Returns TIMESTAMP WITHOUT TIME ZONE created from the specified integer, which must be a UNIX time (number of seconds since 1-Jan-1970).

FROM_UNIXTIME(i, format)

Result type: VARCHAR

Returns i formatted in UNIX time, according to the specified format. For valid formats, see DATE_FORMAT.

FROM_UNIXTIME(1196440219)

returns 2007-11-30 10:30:19.000000

FROM_UNIXTIME(1196440219, '%Y %D %M %h:%i:%s %x')

returns 2007 30th November 10:30:19 2007

Note: Results from this function are from a GMT offset, which may result in a value that is from '1969-12-31 12:00:00.000000' depending on the actual time zone of the server.

GMT_TIMESTAMP

GMT_TIMESTAMP(s)

Result type: Any character data type

Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is ‘yyyy_mm_dd hh:mm:ss GMT’.

For example, the query:

SELECT (GMT_TIMESTAMP (1234567890))

returns:

2009_02_13 23:31:30 GMT

while the query:

(II_TIMEZONE_NAME = AUSTRALIA-QUEENSLAND)

SELECT date(GMT_TIMESTAMP (1234567890))

returns:

14-feb-2009 09:31:30

HOUR

HOUR('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the hour portion of a time or timestamp.

HOUR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12

INTERVAL

INTERVAL(unit,date_interval)

Result type: FLOAT

Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the month, quarter, and year specifications.

For example, the query:

SELECT(INTERVAL('days', '5 years'))

returns:

1826.213

INTERVAL_DIFF

INTERVAL_DIFF('datetime1', 'datetime2')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTERVAL YEAR TO MONTH

Returns the difference between datetime1 and datetime2 expressed as an INTERVAL YEAR TO MONTH.

This complements subtraction of TIMESTAMPs that yields an INTERVAL DAY TO SECOND and subtraction of ANSIDATEs that yields an integer.

INTERVAL_DIFF('2010-1-1', '2015-3-1') returns 5-2

ISDST

ISDST(date)

Returns true if date occurs during Daylight Saving Time in its time zone.

LAST_DAY

LAST_DAY(datetime)

Operand type: DATE, TIMESTAMP

Result type: DATE or TIMESTAMP depending on input

Returns the last day of the month in which the specified date or timestamp falls. Returns NULL if the argument is invalid.

LAST_DAY(DATE '2003-02-05')

returns 2003-02-28

LAST_DAY(DATE '2004-02-05')

returns 2004-02-29

LAST_DAY(TIMESTAMP '2004-01-01 01:01:01')

returns 2004-01-31

LAST_DAY(DATE '2003-03-32')

returns NULL

MICROSECOND

MICROSECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as microseconds.

MICROSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400

MILLISECOND

MILLISECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as milliseconds.

MILLISECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123

MINUTE

MINUTE('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the minute portion of a time or timestamp.

MINUTE(TIMESTAMP '2006-12-15 12:30:55.1234') returns 30

MONTH

MONTH('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the month portion of a date or timestamp.

MONTH(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12

MONTHS_BETWEEN

MONTH_BETWEEN(date1, date2)

Operand type: DATE, TIMESTAMP

Result type: FLOAT8

Returns the number of months between date1 and date2, positive if date2 precedes date1 and negative if date1 precedes date2. If the day of date1 is the same as the day in date2 or both dates are the last days of months, then the result is a whole number of months; otherwise, any time portion is also taken into consideration in the difference, where days are treated as 1/31 of a month.

SELECT MONTHS_BETWEEN(DATE‘2010-06-15’, DATE‘2011-06-15’);

-12.000

SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-05-15’);

1.000

SELECT MONTHS_BETWEEN(DATE‘2011-06-30’, DATE‘2011-05-31’);

1.000

SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-06-01’);

0.452

NANOSECOND

NANOSECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as nanoseconds.

NANOSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400000

QUARTER

QUARTER('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the quarter of the calendar year that corresponds to the date or timestamp. Quarters are numbered 1 through 4.

QUARTER(TIMESTAMP '2006-12-15 12:30:55.1234') returns 4

SECOND

SECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the second portion of a time or timestamp.

SECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 55

SYSDATE

Result type: TIMESTAMP

Returns the current date and time set for the operating system on which the database resides. The format returned depends on the default TIMESTAMP format.

In distributed SQL statements, this function returns the date and time set for the operating system of your local database.

Note: When not used inside a TO_CHAR() wrapper function, SYSDATE always returns a date in the default date format.

Examples:

SELECT SYSDATE\g

2013-08-06 22:28:35.784654-07:00

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')\g

08-06-2013 22:29:58

TIMESTAMPADD

TIMESTAMPADD(interval, n, datetime)

Operand type: Integer, and DATE, TIME, TIMESTAMP

Result type: DATE, TIME, or TIMESTAMP

Returns the datetime after adding the specified number of intervals where:

interval is a keyword from the list: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.

n is an integer expression. The value can be positive or negative as needed.

datetime is a column or a datetime expression that can be a DATE, TIMESTAMP, or TIME value.

TIMESTAMPADD(YEAR, 5, '2010-10-05') returns 2015-10-5

TIMESTAMPDIFF

TIMESTAMPDIFF(interval, datetime1, datetime2)

Operand type: Integer, and DATE, TIME, TIMESTAMP

Result type: Integer type

Returns the integer number of intervals between the two datetimes where:

interval is one of the following keywords: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.

datetime1, datetime2 are columns or datetime expressions whose difference is to be determined in terms of the specific interval, and where datetime1 is a start time, and datetime2 is an end time.

The result is an integer value which can also be negative if datetime1 chronologically follows datetime2.

TIMESTAMPDIFF(YEAR, '2012-01-01', '2008-01-01') returns ‑4

TRUNC

TRUNC(datetime [,str-interval])

TRUNCATE(datetime [,str-interval])

Operand type: datetime is a DATE, TIME, or TIMESTAMP.

Result type: DATE, TIME, or TIMESTAMP

Returns datetime truncated to the unit specified by str-interval. If str-interval is omitted, then datetime is truncated to the nearest day.

The str-interval can be any of the following:

Formats	Description
CC CENTURY CENTURIES	Century
D DY DAY	Starting day of week
DD DDD DAYS J	Day
HH HH12 HR HRS HOUR HOURS	Hour
I IY IYY IYYYY ISO-YR ISO-YRS ISO-YEAR ISO-YEARS	ISO year boundary
IW ISO-WK ISO-WEEK	Same day of week as 1st of ISO year
MI MIN MINS MINUTE MINUTES	Minute
MM MO MON MONTH MONTHS RM MOS	Month
Q QTR QTRS QUARTER QUARTERS	Quarter
W	Same day of week as 1st of month
WW	Same day of week as 1st of year
Y YEAR YY YYY YYYY	Year

The precision of the result is the default precision for the input data type. For example, if TRUNC operates on a TIME column, the precision returned is 0; if TIMESTAMP, then 6.

Examples:

SELECT TRUNC('2013-10-11','CC')\g

2000-01-01

SELECT TRUNC('2013-10-11 23:40:10.123456','DD')\g

2013-10-11 00:00:00.000000

SELECT TRUNC('23:40:10.123456','MIN')\g

23:40:00

SELECT TRUNC('2013-10-11 23:40:10.123456')\g

2013-10-11 00:00:00.000000

UNIX_TIMESTAMP

UNIX_TIMESTAMP()

UNIX_TIMESTAMP([datetime])

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER

Returns a UNIX timestamp (number of seconds since 1 Jan 1970) for the current time (if no argument is specified) or for the specified date.

UNIX_TIMESTAMP(TIMESTAMP '2007-11-30 10:30:19') returns 1196440219

Note: This function returns a valid result up to the year 2038 because the result is always an integer4 value in the range 1 though MAX_I4.

WEEK

WEEK('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Returns the number of the week of the year that the date or timestamp refers to. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. Weeks are numbered 1 to 53.

WEEK('2006-12-15 12:30:55.1234') returns 50

WEEK_ISO

WEEKISO('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER1

Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begins on Monday, but the first week is the week that has the first Thursday of the year. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.

WEEK_ISO is equivalent to WEEK() function with mode 3.

WEEK_ISO(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50

YEAR

YEAR('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the year portion of a date or timestamp.

YEAR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 2006

_DATE

_DATE(s)

Result type: Any character data type

Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.

For example, the query:

SELECT _DATE(123456)

returns:

2-jan-70

Note: This function formats a leading space for day values less than 10.

_DATE4

_DATE4(s)

Result type: Any character data type

Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.

For example, with II_DATE_FORMAT set to US, the query:

SELECT _DATE4(123456)

returns:

02-jan-1970

while with II_DATE_FORMAT set to MULTINATIONAL, the query:

SELECT _DATE4(123456)

returns:

02/01/1970

_TIME

_TIME(s)

Result type: Any character data type

Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated).

For example, the query:

SELECT _TIME(123456)

returns the value 02:17 for the NA-PACIFIC time zone.