Oracle - Date Datatype

Card Puncher Data Processing

About

Use the DATE data type to store point-in-time values (dates and times) in a table. An application that specifies the time for a job might use the DATE data type.

The DATE data type stores:

  • the century,
  • year,
  • month,
  • day,
  • hours,
  • minutes,
  • and seconds.

The valid date range is from January 1, 4712 BC to December 31, 9999 AD.

Use the TIMESTAMP data type to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP.

Formatting

The formatting of a date is controlled by the following parameters :

Snippet with the TO_CHAR function to give a specific format:

TO_CHAR(COLUMN_WITH_DATE_DATATYPE, 'YYYY/MM/DD HH24:MI:SS'),

Date

Substraction

select
  (End_Date - Start_Date)*24*60*60 diff_in_secondes
from
  your_table

Addition

select TO_DATE('20141021 15:34:10','YYYYMMDD HH24:MI:SS')+ 
+(1)          *9 -- Day
+(1/24)       *9 -- Hour
+(1/24/60)    *9 -- Minutes
+(1/24/60/60) *9 -- Seconden
from dual;

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle - DataType

Each value manipulated by Oracle Database has a datatype. Large Object: Data Type Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - Datatype Datetime

Oracle Database supports the following datetime data types: DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE Use the DATE data type to store point-in-time values...
Card Puncher Data Processing
Oracle Database - NLS_DATE_FORMAT

The parameter NLS_DATE_FORMAT control the format of the date datatype. You can see here to see the different format...
Card Puncher Data Processing
Oracle Database - NLS_DATE_LANGUAGE

NLS_DATE_LANGUAGE is a parameter which control the language used in the date format datatype and that you can use to control the implicit datatype conversion
Card Puncher Data Processing
Oracle Database - TIMESTAMP data type

in Oracle. Use the TIMESTAMP data type to store values that are precise tofractional seconds. Its an extension of date that adds fractional second precision. An application that must decide which of...
Time - Date

A date is a moment at the day precision level in a calendar. The date data type is a time data type. It is is an ordered numeric that should not contain any time part date data typedatetime A...



Share this page:
Follow us:
Task Runner