This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: . . Fall 2007 CPE/CSC 365: Introduction to Database Systems Alexander Dekhtyar . . SQL DATE Type Type Conversion SQL DATE Type Oracle’s SQL has a single data type to represent both dates and time: DATE . The DATE data type is unique in how it is used. • A value of DATE type stored in an Oracle database is an number. • A value of DATE type displayed to the user is a string. Basically, Oracle associates a display format with values of the DATE type. • Values of DATE type get converted into a string according to the display format to be shown to users. • When a DATE value is read as input (in an INSERT INTO statement, for example), Oracle assumes that it reads a string. An attempt is made to convert the string into number according to the display format . If the value matches the display format , it is successfully converted and stored. If it does not, an error is reported. Oracle’s default display format for a DATE value is ’DD-MON-YYYY’ or ’DD-MON-YY’ , where DD is days, MON is the three letter abbreviation of the month (e.g., ’JAN’, ’FEB’ , etc.), and YYYY and YY are, respectively, the full year (e.g., 1984 ) and the two last digits of a year (e.g., 07 ). Reading/Inserting DATE data in other formats Often, one needs to input DATE values in formats different from the default. For example, the default display format does not include hours, minutes and seconds. Similarly, it may be important to convert data stored in an Oracle database to a DATE format that is different from the default. These operations are achieved using two SQL’s built-in functions: TO DATE() and TO CHAR() . 1 TO DATE( date , format ): takes as input a string representing the value of type DATE ( date ) and a format specification ( format string), and produces the DATE value from them for storage in the database. TO CHAR( date , format ): takes as input a DATE value ( date , typically, a ta- ble column name) and a format specification ( format string), and produces a string representing the DATE value in the desired format....
View Full Document
This note was uploaded on 05/19/2008 for the course CSC 365 taught by Professor Dekhtyar during the Spring '08 term at Cal Poly.
- Spring '08