Storing and Retrieving Dates : Date « SQL Data Types « Oracle PL/SQL Tutorial





By default, you can supply a date with the format DD-MON-YYYY to the database, where:

  1. DD is a two-digit day such as 05
  2. MON is the first three letters of the month such as FEB
  3. YYYY is a four-digit year such as 1968
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE) ,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(Start_Date) values ('05-FEB-2005')
  2  /

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.










10.6.Date
10.6.1.Process and store a specific date and time.
10.6.2.Use the DATE keyword to supply a date literal to the database.
10.6.3.Cast string to date in where clause
10.6.4.Setting the Default Date Format
10.6.5.Storing and Retrieving Dates
10.6.6.Default time field
10.6.7.Combining TO_CHAR() and TO_DATE() Calls
10.6.8.Converting a DATE to Another Language
10.6.9.Compare date type value in where clause
10.6.10.Query for NULL Date type data
10.6.11.TRUNC(Start_date)
10.6.12.TO_CHAR(start_date, 'MM-DD-YYYY HH24:MI')
10.6.13.Call SYSDATE in insert statement
10.6.14.Check Date format before inserting
10.6.15.Add condition constraint to date type column
10.6.16.Greater than a date
10.6.17.BETWEEN for date type value
10.6.18.Date calculation