SQL Dialects Reference/Functions and expressions/Date and time functions

Date and time functions

See also: Date and time types

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version	Feature	Standard SQL:2011	DB2	Firebird	Ingres	Linter	MSSQL	MySQL Vers. 5.x	MonetDB	Oracle Vers. 11.x	PostgreSQL	SQLite	Virtuoso
?	Current date	CURRENT_DATE	CURRENT DATE CURRENT_DATE	CURRENT_DATE	DATE('TODAY')	SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also)	CAST(GETDATE() AS DATE) CONVERT(DATE, GETDATE())	CURDATE() CURRENT_DATE CURRENT_DATE()	CURRENT_DATE	TRUNC(CURRENT_DATE), TRUNC(SYSDATE)	CURRENT_DATE	CURRENT_DATE	CURDATE
?	Current time	CURRENT_TIME	CURRENT TIME CURRENT_TIME	CURRENT_TIME	TIME(DATE('NOW'))	N/A	N/A	CURTIME, CURRENT_TIME	CURRENT_TIME	N/A	CURRENT_TIME	CURRENT_TIME	CURTIME
?	Current date and time	CURRENT_TIMESTAMP	CURRENT TIMESTAMP CURRENT_TIMESTAMP	CURRENT_TIMESTAMP, 'NOW'	DATE('NOW')	SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP	GETDATE(), CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	CURRENT_DATE, SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	NOW
?	Add interval to date	date + arg	date + arg	arg1 + arg2	arg1 + arg2 arg1 = date, arg2 = time-interval; date+'15 day'	arg1 + arg2 ADD_MONTHS	DATEADD	DATE_ADD	arg1 + interval in days or fractional days	arg1 + interval in days or fractional days ADD_MONTHS() see more	arg1 + arg2	N/A	DATEADD
?	Subtract interval from date	date - arg	date - arg	arg1 - arg2	arg1 - arg2 arg1 = date, arg2 = time-interval; date-'3 month'	arg1 - arg2	DATEDIFF	DATE_SUB	arg1 - interval '1' DAY	arg1 - arg2 (sysdate-interval '1' MONTH)	arg1 - arg2	N/A	DATEDIFF
?	Date difference	(date1 - date2) field	date1 - date2	arg1 - arg2 DATEDIFF	date1 - date2; interval( 'day', date1 - date2)	arg1 - arg2	DATEDIFF	DATEDIFF	CAST(arg1 - arg2 as bigint)/1000 (diff in seconds)	arg1 - arg2 see more see more-2	AGE	N/A	DATEDIFF
?	Last day of month	?	date + 1 MONTH - DAY(date) DAYS	LASTDAYMONTH	LAST_DAY	LAST_DAY	N/A	LAST_DAY	?	LAST_DAY	N/A	N/A	N/A
?	Time zone conversion	date AT TIME ZONE offset	?	N/A	?	N/A	N/A	CONVERT_TZ	?	NEW_TIME	TIMEZONE	N/A	TIMEZONE
?	First weekday after date	?	?	N/A	?	NEXT_DAY	N/A	?	?	NEXT_DAY	N/A	N/A	N/A
?	Convert date to string	CAST (x AS STRING)	TO_CHAR(value, format) VARCHAR_FORMAT(value, format)	CAST(value, datetype) DATETOSTR	DATE_FORMAT(date, format) char(date)	TO_CHAR(value, format)	DATENAME	DATE_FORMAT(value, format)	CAST (x AS STRING)	TO_CHAR	TO_CHAR(value, format)	STRFTIME(format, value)	CAST
?	Convert date to number	N/A	INT(date)	EXTRACT	?	TO_NUMBER(value) DATESPLIT	DATEPART	?	?	TO_NUMBER(TO_CHAR())	DATE_PART	N/A	CAST
?	Convert string to date	CAST (x AS DATE)	DATE(value) TIMESTAMP(value)	CAST	DATE(string)	TO_DATE(value, format) TO_TIMESTAMP(value, format)	CAST	?	CAST, TIMESTAMP 'yyyy-mm-dd HH:mm:ss'	TO_DATE	TO_DATE	N/A	STRINGDATE
?	Extract year from DATE or DATETIME x	EXTRACT(YEAR FROM x)	?	EXTRACT(YEAR FROM x)	YEAR(x) DATE_PART(YEAR, x)^[1]	?	YEAR(x) DATEPART(year, x)	YEAR(x) EXTRACT(YEAR FROM x)	EXTRACT(YEAR FROM x)	EXTRACT(YEAR FROM x)	EXTRACT(YEAR FROM x)	strftime('%Y', x)	?
?	Extract month from DATE or DATETIME x	EXTRACT(MONTH FROM x)	?	EXTRACT(MONTH FROM x)	MONTH(x) DATE_PART(MONTH, x)^[1]	?	MONTH(x) DATEPART(month, x)	MONTH(x) EXTRACT(MONTH FROM x)	EXTRACT(MONTH FROM x)	EXTRACT(MONTH FROM x)	EXTRACT(MONTH FROM x)	strftime('%m', x)	?
?	Extract day of month from DATE or DATETIME x	EXTRACT(DAY FROM x)	?	EXTRACT(DAY FROM x)	DAY(x) DATE_PART(DAY, x)^[1]	?	DAY(x) DATEPART(day, x)	DAYOFMONTH(x) DAY(x) EXTRACT(DAY FROM x)	EXTRACT(DAY FROM x)	EXTRACT(DAY FROM x)	EXTRACT(DAY FROM x)	strftime('%d', x)	?
?	Extract hour (0…23) from TIME or DATETIME x	EXTRACT(HOUR FROM x)	?	EXTRACT(HOUR FROM x)	HOUR(x) DATE_PART(HOUR, x)^[1]	?	DATEPART(hour, x)	HOUR(x) EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	strftime('%H', x)	?

↑ ^a ^b ^c ^d These functions are only applicable to ingresdate data type in Ingres; normal dates, represented by ansidate datatype should be converted to ingresdate first.

[ingresdate-1] These functions are only applicable to ingresdate data type in Ingres; normal dates, represented by ansidate datatype should be converted to ingresdate first.

[1]