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

From Wikibooks, the open-content textbooks collection

Jump to: navigation, search

[edit] Date and time functions

ANSI/ISO SQL Level Feature-ID Function DB2 SQLite MySQL PostgreSQL Firebird OpenLink Virtuoso Oracle MSSQL Linter
Current Date NOW() sysdate GETDATE() SYSDATE
Date addition date + arg N/A DATE_ADD arg1 + arg2 arg1 + arg2 DATEADD arg1 + arg2 (sysdate+interval '1' DAY)see more DATEADD arg1 + arg2
ADD_MONTHS
Date subtraction date - arg N/A DATE_SUB arg1 - arg2 arg1 - arg2 DATEDIFF arg1 - arg2 (sysdate-interval '1' MONTH) DATEDIFF arg1 - arg2
Date difference date1 - date2 N/A DATEDIFF AGE arg1 - arg2 DATEDIFF arg1 - arg2 see more see more-2 DATEDIFF arg1 - arg2
Last day of month date + 1 MONTH - DAY(date) DAYS N/A LAST_DAY N/A LASTDAYMONTH[1] N/A LAST_DAY N/A LAST_DAY
Time zone conversion N/A CONVERT_TZ TIMEZONE N/A TIMEZONE NEW_TIME N/A N/A
First weekday after date N/A N/A N/A N/A NEXT_DAY N/A NEXT_DAY
SQL:2003/200n (core) F051-04 Convert date to string TO_CHAR(value, format)
VARCHAR_FORMAT(value, format)		 STRFTIME(format, value) DATE_FORMAT(value, format) TO_CHAR(value, format) CAST(value, datetype)
DATETOSTRTemplate:Ref num		 CAST TO_CHAR DATENAME TO_CHAR(value, format)
SQL:2003/200n (core) F051-04 Convert date to number INT(date) N/A DATE_PART EXTRACT CAST TO_NUMBER(TO_CHAR()) DATEPART TO_NUMBER(value)
DATESPLIT
SQL:2003/200n (core) F051-04 Convert string to date DATE(value)
TIMESTAMP(value)		 N/A TO_DATE CAST STRINGDATE TO_DATE CAST TO_DATE(value, format)
TO_TIMESTAMP(value, format)
SQL:2003/200n (core) F051-06 Get current date CURRENT_DATE CURRENT_DATE CURDATE, CURRENT_DATE CURRENT_DATE CURRENT_DATE CURDATE SYSDATE, CURRENT_DATE (incl time also) GETDATE(), CURRENT_DATE (incl time also) SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also)
SQL:2003/200n (core_ F051-01 Get current time CURRENT_TIME CURRENT_TIME CURTIME, CURRENT_TIME CURRENT_TIME CURRENT_TIME CURTIME N/A N/A N/A
SQL:2003/200n (core) F051-01 Get current date and time CURRENT_TIMESTAMP CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP NOW(), CURRENT_TIMESTAMP CURRENT_TIMESTAMP, 'NOW' NOW SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP GETDATE(), CURRENT_TIMESTAMP SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP


 

Retrieved from "http://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/Date_and_time_functions"