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. 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.
Oracle Database SQL