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

Date and time functions

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	DB2	Firebird	Ingres	Linter	MSSQL	MySQL	MonetDB	Oracle	PostgreSQL	SQLite	Virtuoso
Core SQL:1999	Current date	CURRENT_DATE	CURRENT DATE CURRENT_DATE	CURRENT_DATE	DATE('TODAY')	SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also)	N/A	CURDATE() CURRENT_DATE CURRENT_DATE()	CURRENT_DATE	CURRENT_DATE	CURRENT_DATE	CURRENT_DATE	CURDATE
Core SQL:1999	Current time	CURRENT_TIME	CURRENT TIME CURRENT_TIME	CURRENT_TIME	TIME(DATE('NOW'))	N/A	N/A	CURTIME, CURRENT_TIME	?	N/A	CURRENT_TIME	CURRENT_TIME	CURTIME
Core SQL:1999	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	?	SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP	NOW(), CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	NOW
Core SQL:1999	Date addition	date + arg	date + arg	arg1 + arg2	?	arg1 + arg2 ADD_MONTHS	DATEADD	DATE_ADD	?	arg1 + arg2 (sysdate+interval '1' DAY)see more	arg1 + arg2	N/A	DATEADD
Core SQL:1999	Date subtraction	date - arg	date - arg	arg1 - arg2	?	arg1 - arg2	DATEDIFF	DATE_SUB	?	arg1 - arg2 (sysdate-interval '1' MONTH)	arg1 - arg2	N/A	DATEDIFF
Core SQL:1999	Date difference	(date1 - date2) field	date1 - date2	arg1 - arg2 DATEDIFF	?	arg1 - arg2	DATEDIFF	DATEDIFF	?	arg1 - arg2 see more see more-2	AGE	N/A	DATEDIFF
?	Last day of month	?	date + 1 MONTH - DAY(date) DAYS	LASTDAYMONTH	?	LAST_DAY	N/A	LAST_DAY	?	LAST_DAY	N/A	N/A	N/A
Core SQL:1999	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
SQL:2003/200n (core)	Convert date to string	?	TO_CHAR(value, format) VARCHAR_FORMAT(value, format)	CAST(value, datetype) DATETOSTR	?	TO_CHAR(value, format)	DATENAME	DATE_FORMAT(value, format)	?	TO_CHAR	TO_CHAR(value, format)	STRFTIME(format, value)	CAST
SQL:2003/200n (core)	Convert date to number	?	INT(date)	EXTRACT	?	TO_NUMBER(value) DATESPLIT	DATEPART	?	?	TO_NUMBER(TO_CHAR())	DATE_PART	N/A	CAST
SQL:2003/200n (core)	Convert string to date	?	DATE(value) TIMESTAMP(value)	CAST	?	TO_DATE(value, format) TO_TIMESTAMP(value, format)	CAST	?	?	TO_DATE	TO_DATE	N/A	STRINGDATE
?	Extract year from DATE or DATETIME x	?	?	EXTRACT(YEAR FROM x)	YEAR(x) DATE_PART(YEAR, x)	?	YEAR(x) DATEPART(year, x)	YEAR(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)	MONTH(x) DATE_PART(MONTH, x)	?	MONTH(x) DATEPART(month, x)	MONTH(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)	DAY(x) DATE_PART(DAY, x)	?	DAY(x) DATEPART(day, x)	DAYOFMONTH(x) DAY(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)	HOUR(x) DATE_PART(HOUR, x)	?	DATEPART(hour, x)	HOUR(x) EXTRACT(HOUR FROM x)	?	EXTRACT(HOUR FROM x)	EXTRACT(HOUR FROM x)	strftime('%H', x)	?