Skip to content Skip to main navigation Skip to footer

Date Functions in SQL

Objectives

This tutorial covers the following objectives:

  • Demonstrate the use of SYSDATE and date functions
  • State the implications for world businesses to be able to easily manipulate data stored in date format

Purpose

  • Have you ever wondered how many days remain in the school year or how many weeks there are until graduation?
  • Because the Oracle database stores dates as numbers, you can easily perform calculations on dates using addition, subtraction, and other mathematical operators.
  • Businesses depend on being able to use date functions to schedule payrolls and payments, track employee performance reviews and years of service, or keep track of orders and shipments.
  • All of these business needs are easily handled using simple SQL date functions.

Displaying Dates

  • The default display and input format for dates is: DD-Mon-YYYY
  • For example: 02-Dec-2014.
  • However, the Oracle database stores dates internally with a numeric format representing the century, year, month, day, hour, minute, and second.
  • Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999 A.D.
  • This represents the range of dates that you can store successfully in an Oracle database.

SYSDATE

  • SYSDATE is a date function that returns the current database server date and time.
  • Use SYSDATE to display the current date, use the DUAL table.
SELECT SYSDATE
FROM dual;
Date Functions in SQL

DATE Data Type

  • The DATE data type always stores year information as a four-digit number internally: two digits for the century and two digits for the year.
  • For example, the Oracle database stores the year as 1996 or 2004, not just as 96 or 04.
  • In previous versions, the century component was not displayed by default.
  • However, due to changing business requirements around the world, the 4-digit year is now the default display.

Working with Dates

Working with Dates

These examples show the use of arithmetic operations with dates.

Date Functions

  • The date functions shown in the table operate on Oracle dates.
  • All of the date functions return a value with a DATE data type except the MONTHS_BETWEEN function, which returns a numeric data type value.
Date Functions

For the examples that follow, the SYSDATE was 01-Jul-2017.

  • MONTHS_BETWEEN: takes 2 DATE arguments and returns the number of calendar months between the 2 dates.
  • If the first argument is an earlier date than the second, the number returned is negative.
Date Functions

This example returns employees that were hired more than 240 months (20 years) ago

  • ADD_MONTHS: takes 2 arguments, a DATE and a number. Returns a DATE value with the number argument added to the month component of the date.
  • If the number supplied is negative, the function will subtract that number of months from the date argument
Date Functions

This example adds 12 months to the current date.

  • NEXT_DAY: takes 2 arguments, a DATE and a weekday and returns the DATE of the next occurrence of that weekday after the DATE argument.
Date Functions
  • LAST_DAY: takes a DATE argument and returns the DATE of the last day of the month for the DATE argument.
Date Functions
  • ROUND: returns a DATE rounded to the unit specified by the second argument.
Date Functions
  • TRUNC: returns a DATE truncated to the unit specified by the second argument.
Date Functions

If ‘month’ is the unit specified, the date returned will be the first day of the month for the DATE argument.

If ‘Year’ is the unit specified, TRUNC will return the first day of the year for the DATE argument.

  • Here is an example of a query using multiple date functions.
  • The output is displayed below.
SELECT employee_id, hire_date,
ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS TENURE,
ADD_MONTHS (hire_date, 6) AS REVIEW,
NEXT_DAY(hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) > 36;
  • The result set from this query returns 20 rows including:
Date Functions

Terminology

Key terms used in this tutorialincluded:

  • ADD_MONTHS
  • LAST_DAY
  • MONTHS_BETWEEN
  • NEXT_DAY
  • SYSDATE
  • ROUND
  • TRUNC

Summary

In this tutorial, you should have learned how to:

  • Select and apply the single-row functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC that operate on date data
  • Explain how date functions transform Oracle dates into date data or numeric values
  • Demonstrate proper use of the arithmetic operators with dates
  • Demonstrate the use of SYSDATE and date functions
  • State the implications for world businesses to be able to easily manipulate data stored in date format

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *