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

Date and time functions edit

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) ?
  1. 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.