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

From Wikibooks, open books for an open world
Jump to: navigation, search

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) N/A CURDATE()
CURRENT_DATE
CURRENT_DATE()
CURRENT_DATE CURRENT_DATE 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 SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP CURRENT_TIMESTAMP NOW
 ? Date addition date + arg date + arg arg1 + arg2  ? arg1 + arg2
ADD_MONTHS
DATEADD DATE_ADD arg1 + interval '1' DAY arg1 + arg2 (sysdate+interval '1' DAY)see more arg1 + arg2 N/A DATEADD
 ? Date subtraction date - arg date - arg arg1 - arg2  ? 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
 ? 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
 ? 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
 ? 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  ? 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.