Oracle Tips by Burleson

Date Functions

Dates are stored in the database as a number that contains both the calendar data information and the time information.  We already discussed date math, where the unit of measure is one day.  Date functions allow you to modify and compare date data types.  Dates can be tricky.  If you use SYSDATE to insert date columns in tables, you will not only get the date component but also the time component.   

If you want to see all the records from today, a query based on today’s date will not match any rows in the database.  Basically, the times components of the two dates will not match.  This section will explain how to work with date data types and the next section will explain converting characters to dates and back again.  When working with calendars, you also have the problem that all months do not have the same number of days in them.  If you have a date and want the same date in three months, it becomes problematic.

months_between (l,e)

This function returns the months between two dates.  If I wanted to know how many months an employee has worked for the company, I can use this function.  There is an emp_hire_date in the emp table.

SELECT
  MONTHS_BETWEEN(SYSDATE,EMP_DATE_OF_HIRE)
FROM
  emp;

MONTHS_BETWEEN(SYSDATE,EMP_DATE_OF_HIRE)
----------------------------------------                              58.7710805
                              70.7710805
                              34.7710805
                              46.7710805
                              34.7710805
                              82.7710805
                               106.77108
                               154.77108
                               178.77108
                               166.77108

10 rows selected. 

Notice that it returns the fraction of a month.  You could use truncor round to make the results more readable.

add_months (d,n)

The add_months function gives you the same day, n number of months away.  The n can be positive or negative.

SELECT
  SYSDATE,
  ADD_MONTHS(SYSDATE,1),
  ADD_MONTHS(SYSDATE,2),
  ADD_MONTHS(SYSDATE,3),
  ADD_MONTHS(SYSDATE,4),
  ADD_MONTHS(SYSDATE,5),
  ADD_MONTHS(SYSDATE,6)
FROM
  dual;

SYSDATE   ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-FEB-05 24-MAR-05 24-APR-05 24-MAY-05 24-JUN-05 24-JUL-05

SELECT
  SYSDATE,
  ADD_MONTHS(SYSDATE,-1),
  ADD_MONTHS(SYSDATE,-2),
  ADD_MONTHS(SYSDATE,-3),
  ADD_MONTHS(SYSDATE,-4),
  ADD_MONTHS(SYSDATE,-5),
  ADD_MONTHS(SYSDATE,-6)
FROM
  dual; 

SYSDATE   ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-DEC-04 24-NOV-04 24-OCT-04 24-SEP-04 24-AUG-04 24-JUL-04

last_day (d)

The last_day function returns the last day of the month of the date d.  If you want to find the first day of the next month, simply add one to the last_day results.

SELECT
  SYSDATE,
  LAST_DAY(SYSDATE) EOM,
  LAST_DAY(SYSDATE)+1 FOM
FROM dual;

SYSDATE   EOM       FOM
--------- --------- ---------
24-JAN-05 31-JAN-05 01-FEB-05

next_day (d, day_of_week)

The next_day function returns the date of the day_of_week  after date d.  day_of_week can be the full name or abbreviation.  Below, we get the date for next Monday, next Friday, and the first Tuesday of next month. 

SELECT
  SYSDATE,
  NEXT_DAY(SYSDATE,'MONDAY') "Next Mon",
  NEXT_DAY(SYSDATE,'FRIDAY') "Next Fri",
  NEXT_DAY(LAST_DAY(SYSDATE)+1,'TUESDAY') "First Tue"
FROM dual;

SYSDATE   Next Mon  Next Fri  First Tue
--------- --------- --------- ---------
24-JAN-05 31-JAN-05 28-JAN-05 08-FEB-05

round (d, format)

We talked about the round function as a numeric function but it is also a date function.  The round function returns the date rounded to the format. 

SELECT
  SYSDATE,
  ROUND(SYSDATE,'MONTH') Month,
  ROUND(SYSDATE,'YEAR')  Year
FROM
  dual; 

SYSDATE   MONTH     YEAR
--------- --------- ---------
24-JAN-05 01-FEB-05 01-JAN-05

Notice that SYSDATE is past midmonth so the month was rounded to the next month.  We are not past midyear, however, so the year was rounded to the beginning of the current year.

trunc (d, format)

As with the numeric trunc, the date version simply truncates the date to the level specified in the format. 

SELECT
  SYSDATE,
  TRUNC(SYSDATE,'MONTH') Month,
  TRUNC(SYSDATE,'YEAR')  Year
FROM
  dual;

SYSDATE   MONTH     YEAR
--------- --------- ---------
24-JAN-05 01-JAN-05 01-JAN-05

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm