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.

Function syntax:
Now()
Now example:
MySQL:
SELECT Now() now;
The result should be something similar:
now
2016-06-20 09:23:00
PostgreSQL:
SELECT Now()::timestamp now;
The result should be something similar:
now
timestamp without time zone
2016-06-20 10:01:14.017058

GETDATE() function

Return the current date and time in SQL server.
Function syntax:
GETDATE()
Getdate example:
SQL server:
SELECT GETDATE() now;
The result should be something similar:
now
2016-06-20 09:23:00:988

Current_date function

Return the current date and time in Oracle.
Function syntax:
current_date
current_date example:
Oracle:
SELECT CURRENT_DATE  now FROM DUAL;
The result should be something similar:
now
2016-06-20 09:23:00:0

DATE_ADD() & DATE_SUB() function

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 in MySQL.
Function syntax:
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 */
The unit can be one of the following:
Unit Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_ADD example:
MySQL:
SELECT Now() now, DATE_ADD(Now(),interval 3 day) now3;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-23 09:23:00
DATE_SUB example:
MySQL:
SELECT Now() now, DATE_SUB(Now(),interval 3 day) now3;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-17 09:23:00

DATEADD() function

This function is used to calculate a new date from a date by either adding a type of date units or subtract a type of date units in SQL server.
Function syntax:
DATEADD(datepart , number , date );
The datepart can be one of the following:
datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
DATEADD add example:
SQL server:
SELECT GETDATE() now, DATEADD(day,3,GETDATE()) now3;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-23 09:23:00
DATEADD subtract example:
SQL server:
SELECT GETDATE() now, DATEADD(day,-3,GETDATE()) now3;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-17 09:23:00

Add days to date or Subtract days from date function

This functionality is used to calculate a new date from a date either by add days or part of a day or subtract days or a portion of a day in Oracle.
Function syntax:
date + unit of day;
The unit of day can be one of the following:
unitMeaning
11 day
1/241 hour
1/14401 minute
1/864001 second
3030 days
365365 days
Add to date example:
Oracle:
SELECT CURRENT_DATE now,  CURRENT_DATE + 3 now3 FROM dual;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-23 09:23:00
Subtract from date example:
Oracle:
SELECT CURRENT_DATE now,  CURRENT_DATE - 3 now3 FROM dual;
The result should be:
now now3
2016-06-20 09:23:00 2016-06-17 09:23:00

Add units to date or Subtract units from date function

This functionality is used to calculate a new date from a date either by add date interval units or subtract date interval units in PostgreSQL.
Function syntax:
date [+ | -] interval '(units)'
You can specify one or more of these units :
unitsMeaning
n yearadd or subtract n years from a date
n monthadd or subtract n months from a date
n day(s)add or subtract n days from a date
n houradd or subtract n hours from a date
n minuteadd or subtract n minutes from a date
n secondadd or subtract n seconds from a date
Add to date example:
PostgreSQL:
SELECT Now()::timestamp now,  Now()::timestamp + interval '3 days 2 hour' now3 
The result should be:
now
timestamp without time zone
now3
timestamp without time zone
2016-06-30 11:02:36.503056 2016-07-03 13:02:36.503056
Subtract from date example:
PostgreSQL:
SELECT Now()::timestamp now,  Now()::timestamp - interval '3 days 2 hour' now3 
The result should be:
now
timestamp without time zone
now3
timestamp without time zone
2016-06-30 11:05:39.88056 2016-06-27 09:05:39.88056

EXTRACT() function

This function is used to extract partial information in a date in both MySQL, PostgreSQL and Oracle.
Function syntax:
EXTRACT(unit FROM date);
The unit can be one of the following in Oracle:
Unit Value
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR
The unit can be one of the following in MySQL:
Unit Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
The unit can be one of the following in PostgreSQL:
Unit Value
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
millennium
century
decade
timezone
timezone_hour
timezone_minute
dow (The day of the week as Sunday(0) to Saturday(6))
isodow (The day of the week as Monday(1) to Sunday(7))
doy (The day of the year (1 - 365/366))
isoyear (The ISO 8601 year that the date falls in)
EXTRACT example:
MySQL, Oracle, PostgreSQL:
SELECT Order_id, Customer_id, EXTRACT(month FROM ORDER_DATE ) ant_month FROM orders;
The result should be:
order_id customer_id ant_month
70001 10001 10
70002 10002 6
70003 10003 8

DATEPART() function

This function is used to extract partial information in a date in SQL server.
Function syntax:
DATEPART(datepart , date);
The datepart can be one of the following:
datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, isoww
DATEPART example:
SQL server:
SELECT Order_id, Customer_id, DATEPART(month ,ORDER_DATE ) month FROM orders;
The result should be:
order_id customer_id month
70001 10001 10
70002 10002 6
70003 10003 8
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.