SQL Date and Time Functions.

SQL built-in Date and Time functions

Summary

• Date and Time functions perform manipulation of dates and times that normally are contained in a table column.
• There is a differentiated range of built-in date and time functions in the various RDBMS.
• You will in most DBMS find common functionality in handling date and time, but the solution to achieve the results will vary.
• Here is a collection of the most important built-in date and time functions:

  Function	RDBMS	Description
  NOW()	MySQL, PostgreSQL	Returns the current date and time. PostgreSQL will return the current date and time with timezone.
  GETDATE()	SQL server
  CURRENT_DATE	Oracle
  DATE_ADD() & DATE_SUB()	MySQL	These functions are used to calculate a new date from a date by either adding a type of date units or subtract a type of date units.
  DATEADD()	SQL server
  {date +/- part of day}	Oracle
  {date +/- interval(units)}	PostgreSQL
  EXTRACT()	MySQL, Oracle, PostgreSQL	These functions are used to extract partial information in a date.
  DATEPART()	SQL server

NOW() function

Return the current date and time in MySQL.
Return the current date and time and timezone in PostgreSQL.

Now()

SELECT Now() now;

SELECT Now()::timestamp now;

GETDATE() function

GETDATE()

SELECT GETDATE() now;

Current_date function

current_date

SELECT CURRENT_DATE  now FROM DUAL;

DATE_ADD() & DATE_SUB() function

DATE_ADD(date,INTERVAL expr unit); /* to add date units to a date */
DATE_SUB(date,INTERVAL expr unit); /* subtract date units from a date */

SELECT Now() now, DATE_ADD(Now(),interval 3 day) now3;

SELECT Now() now, DATE_SUB(Now(),interval 3 day) now3;

DATEADD() function

DATEADD(datepart , number , date );

SELECT GETDATE() now, DATEADD(day,3,GETDATE()) now3;

SELECT GETDATE() now, DATEADD(day,-3,GETDATE()) now3;

Add days to date or Subtract days from date function

date + unit of day;

SELECT CURRENT_DATE now,  CURRENT_DATE + 3 now3 FROM dual;

SELECT CURRENT_DATE now,  CURRENT_DATE - 3 now3 FROM dual;

Add units to date or Subtract units from date function

date [+ | -] interval '(units)'

SELECT Now()::timestamp now,  Now()::timestamp + interval '3 days 2 hour' now3 

SELECT Now()::timestamp now,  Now()::timestamp - interval '3 days 2 hour' now3 

EXTRACT() function

EXTRACT(unit FROM date);

SELECT Order_id, Customer_id, EXTRACT(month FROM ORDER_DATE ) ant_month FROM orders;

DATEPART() function

DATEPART(datepart , date);

SELECT Order_id, Customer_id, DATEPART(month ,ORDER_DATE ) month FROM orders;