DATEs Oracle supports the storage of both date and time data but its implementation does not follow the SQL standard. Under the SQL standard DATE and TIME are two separate datatypes. However, Oracle implements only DATE which has both a date component (year, month and day) and a time component (hour, minute and seconds). For those of you familiar with Access you will recognise the similarity.
If you specify a DATE value without a time component, then the default time is 12:00:00 (midnight). If you specify a DATE value without a date component, then the default date is the first day of the current month.
DATEs If your queries use a DATE format without a time portion, it is important that the time fields in the DATE column are set to zero (that is, midnight). Otherwise, Oracle may not return the query results you expect.
You can remove the time component of a DATE by using the TRUNC function. Always use TRUNC if you are not sure that the time fields in the DATE column are set to zero.
For example: Find the customers who have invoices dated 16-Sep-2004
SELECT CustABN
FROM INVOICE
WHERE TRUNC(InvDate) = DATE '2004-9-16'
DATEsThe default format to display DATEs is "DD-MON-YY" e.g. 25-APR-08
The default DATE format is changed by calling TO_CHAR with the required DATE format. For example, if 25-APR-08 was stored in dateValue
To_CHAR(dateValue, 'DD/MM/YYYY')
Would display:
25/04/2008
The general form of To_CHAR is:
TO_CHAR(<date>, '<format>')
where the <format> string can be formed from over 40 options. Some of the more popular ones include:
| MM | Numeric month (e.g. 07) |
| MON | Abbreviated month name (e.g. JUL) |
| MONTH | Full month name (e.g. JULY) |
| DD | Day of month (e.g. 24) |
| DY | Abbreviated name of day (e.g. FRI) |
| YYYY | Four-digit year (e.g. 1998) |
| YY | Last two digits of the year (e.g. 98) |
| AM (or PM) | Meridian indicator |
| HH | Hour of day (1-12) |
| HH24 | Hour of day (0-23) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
Whenever Oracle expects a DATE value, it should be in the default DATE format "DD-MON-YY". For example, a WHERE clause of a SELECT query may include the line:
WHERE InvDate = '25-APR-08'
Alternatively, you may change the format by using the TO_DATE function with the required format.
For example:
WHERE InvDate = to_date('2008/04/25:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')
The general form of TO_DATE is:
TO_DATE(<string>, '<format>')
where the <format> string has the same options as in TO_CHAR.