Data Manipulation   «Prev  Next»

Lesson 6 Date/time functions for date manipulation
Objective Identify valid date-related functions and Oracle's standard date format.

Oracle Date and Time Functions

This lesson looks at a few of the more commonly used date functions that work with the date portion of the date datatype. Since the date datatype also contains time, the next lesson illustrates functions you can use to manipulate the time portion of a date.

Date functions

The default display format for dates in Oracle is: DD-MON-YY. However, dates are actually stored in the database as a large number that facilitates date comparisons and date arithmetic. There are many functions for manipulating dates. The following table lists them all.

Function Syntax Description
ADD_MONTHS ADD_MONTHS(col1, n) Add n months to col1
LAST_DAY LAST_DAY(col1) Date of the last day of the month for col1
MONTHS_BETWEEN MONTHS_BETWEEN(col1, col2) Number of months between two dates. Returns fraction of month if days of the two dates are different.
NEW_TIME NEW_TIME(col1, zone1, zone2) Converts date and time from zone1 to zone2
NEXT_DAY NEXT_DAY(col1, day) Returns the date of the next day named by day (such as 'MONDAY')
ROUND ROUND(col1,[round_to]) Returns the date rounded to the nearest day by default; can round to nearest month, quarter, year, hour, and so on based on round_to format
TRUNC TRUNC(col1, [trunc_to]) Returns the date with the time removed; can truncate to nearest month, quarter, year, hour, and so on based on trunc_to format
TO_CHAR TO_CHAR(col1, date_format) Returns date and/or time based on format listed

TO_CHAR

You can display an Oracle date as any of dozens of format combinations. Just use this basic layout: TO_CHAR(col1,'format')
The format parameter is always enclosed in single quotes and uses symbols defining how the date is displayed. Here are some of the date format symbols:

Format parameter Description
MM Month in numbers (01 - 12)
Month Month spelled out with initial capital letter (January - December)
MONTH Month spelled in all capital letters (JANUARY - DECEMBER)
MON Three character abbreviation of month in capital letters (JAN - DEC)
DD Day in numbers (01 - 31)
DDth 01st, 02nd, and so on
DDD number of day in the year (1 - 366)
Day Day spelled out with initial capital letter (Monday - Sunday)
DAY Day of week in all capital letters (MONDAY - SUNDAY)
YY Last two digits of year (00 - 99)
Year Year spelled out with initial capital (such as Two Thousand)
RR Rounded year; if year is <50, it will round up to the next century if current year is > 50.
HH or HH12 Hour (1 - 12)
HH24 Hour (0 - 23)
MI Minutes (00 - 59)
PM Meridian in capital letters (AM or PM)
P.M. Meridian (A.M. or P.M.)
SS Seconds (00 - 59)

There are many more, but these are the major ones.
The following Slide Show shows several examples of the TO_CHAR() function with a variety of date formats, plus some date arithmetic.

1)This example shows the day and month spelled out and they year displayed as a four-digit year.
1) This example shows the day and month spelled out and the year displayed as a four-digit year. Notice that you can add commas and spaces as placeholders anywhere in the format parameters.

2) You can add or subtract days to a date with a plus sign or minus sign
2) You can add or subtract days to a date with a plus sign or minus sign

3) We can find the difference between two dates
3) You can find the difference between two dates by subtracting them. When the difference is calculated, the time stored with each date is taken into account. The difference is displayed as days and fractions of days. 
SELECT
SALES_DATE,
SALES_DATE - TO_DATE('15-MAR-99') DIFF
* FROM CUSTOMER_SALE

When adding or subtracting years and months, you get different behavior from using ADD_MONTHS, which operates on values of type DATE, than from using interval arithmetic on the timestamp types
4) You can also use the functions, such as NEXT_DAY, to calculate dates.
4) You can also use the functions, such as NEXT_DAY, to calculate dates. For example, here is a query and the results showing the ADD_MONTHS and NEXT_DAY functions. 

SELECT SALES_DATE,
ADD_MONTHS(SALES_DATE,3) THREE_MONTHS,
NEXT_DAY(SALES_DATE, 'MONDAY') NEXT_MONDAY
FROM CUSTOMER_SALE

  1. This example shows the day and month spelled out and the year displayed as a four-digit year.
  2. You can add or subtract days to a date with a plus sign or minus sign
  3. You can find the difference between two dates by subtracting them. When the difference is calculated, the time stored with each date is taken into account.
  4. You can also use the functions, such as NEXT_DAY, to calculate dates.

Oracle Date formats and date Arithmetic

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database. When you convert a character string into a date or number, a format model determines how Oracle Database interprets the string. In SQL statements, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to specify:
  1. The format for Oracle to use to return a value from the database
  2. The format for a value you have specified for Oracle to store in the database
For example:
  1. The datetime format model for the string '17:45:29' is 'HH24:MI:SS'.
  2. The datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'.
  3. The number format model for the string '$2,304.25' is '$9,999.99'.

ADD_MONTHS

The ADD_MONTHS function returns the date resulting from date plus integer months. SQL syntax 
ADD_MONTHS(Date,Integer)
Parameters: ADD_MONTHS has the parameters:
Parameter Description
Date A datetime value or any value that can be converted to a datetime value.
Integer An integer or any value that can be converted to an integer.

Description
  1. The return type is always DATE regardless of the data type of date. Supported data types are DATE, TIMESTAMP, ORA_TIMESTAMP and ORA_DATE.
  2. Data types TIME, TT_TIME, TT_DATE and TT_TIMESTAMP are not supported.
  3. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

Y2K and the Oracle date

By the time this course reaches you, it may be the year 2023.
The Oracle date has always contained all four digits of the year. Even when your input only requires two digits entered for the year, Oracle adds in the century before storing the date in the database. In the next lesson, you will learn how to identify valid time-related date/time functions.

Ad Oracle Database SQL