RelationalDBDesign RelationalDBDesign


Data Manipulation   «Prev 

Oracle Date formats and date Arithmetic

Format Models

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. 4 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.
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.

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

SELECT
SALES_DATE,
SALES_DATE - TO_DATE('15-MAR-99') DIFF
* FROM CUSTOMER_SALE
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.

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
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