Simple Examples of Storing and Retrieving Dates

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

• DD is a two-digit day such as 05

• MON is the first three letters of the month such as FEB

• YYYY is a four-digit year such as 1968

Let s take a look at an example of adding a row to the customers table, which contains a DATE column named dob . The following INSERT statement adds a row to the customers table, setting the dob column to 05-FEB-1968:

 INSERT INTO customers (   customer_id, first_name, last_name, dob, phone ) VALUES (   6, 'Fred', 'Brown', '05-FEB-1968', '800-555-1215' ); 

You can also use the DATE keyword to supply a date literal to the database. Your date must use the ANSI standard date format YYYY-MM-DD , where:

• YYYY is a four-digit year

• MM is a two-digit month from 1 to 12

• DD is a two-digit day

  Tip  

  ANSI standard dates have the advantage that your INSERT statement could potentially run against non-Oracle databases.

For example, to specify a date of October 25, 1972, you use DATE ˜1972-10-25 . The following INSERT statement adds a row to the customers table, supplying DATE ˜1972-10-25 to the dob column:

 INSERT INTO customers (   customer_id, first_name, last_name, dob, phone ) VALUES (   7, 'Steve', 'Purple', DATE '1972-10-25', '800-555-1215' ); 

By default, the database outputs dates in the format DD-MON-YY , where YY are the last two digits of the year. For example, the following query retrieves rows from the customers table and then performs a ROLLBACK to undo the results of the INSERT statements; notice the twodigit years in the dob column returned by the query:

  SELECT *   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME  DOB       PHONE ----------- ---------- ---------- --------- ------------           1 John       Brown      01-JAN-65 800-555-1211           2 Cynthia    Green      05-FEB-68 800-555-1212           3 Steve      White      16-MAR-71 800-555-1213           4 Gail       Black                800-555-1214           5 Doreen     Blue       20-MAY-70           6 Fred       Brown      05-FEB-68 800-555-1215           7 Steve      Purple     25-OCT-72 800-555-1215  ROLLBACK;  

Customer #4 s dob is null and is therefore blank in the previous output.

Note  

If you actually ran the two INSERT statements shown earlier using SQL*Plus, make sure you undo the changes by executing the ROLLBACK statement. That way, you ll keep the database in its initial state, and the output from your queries will match mine. If you forget to roll back, you can remove your new rows using DELETE , or you can simply rerun the store_schema.sql script.

In this section, you saw some simple examples of using dates that use default formats. You ll learn how to provide your own date formats in the following section and see how to convert datetimes from one type to another.