Date and Time Functions in SQL

Every DBMS allows you to store date and time values in tables using specific date data types and in specific formats. Each implementation follows its own storage format to save the date and time, for example, Oracle’s default display and input format for any date is DD-MON-YY. Unfortunately, this default storage varies among different implementations and is, therefore, least portable.

Similar to the character functions, date and time functions are also used to manipulate the representation of data. These function are used not only to present date and time data in some desirable format, but are also helpful in comparing date values, and computing intervals between dates.

To start with, here is an example in Oracle which fetches current date from the system, followed by a list of functions that perform the same task for other platforms.

    SQL Statement :

SELECT    SYSDATE

FROM        DUAL;

    Output :

The following table lists respective functions as employed by various DBMSs to get current system date:

DBMS	FUNCTION	SQL STATEMENT	OUTPUT (Format)
MySQL/MariaDB	CURDATE()	SELECT CURDATE();	2014-11-19 (YYYY-MM-DD)
PostgreSQL	CURRENT_DATE	SELECT CURRENT_DATE;	2014-11-19 (YYYY-MM-DD)
Microsoft Access	NOW()	SELECT Now() FROM Products;	11/19/2014 2:28:17 AM
Microsoft SQL Server	GETDATE()	SELECT GETDATE();	2014-11-19 13:10:02.047
SQLite	DATE()	SELECT DATE('now');	2014-11-19 (YYYY-MM-DD)
DB2	CURRENT DATE	SELECT CURRENT DATE FROM sysibm.sysdummy1;	2014-11-19 (YYYY-MM-DD)