CRITICAL SKILL 2.7 Use Dates and Data Functions (Formatting and Chronological)

Team Fly

Page 54

CRITICAL SKILL 2.7
Use Dates and Data Functions (Formatting and Chronological)

Date is the next commonest type of data you'll find in an Oracle database after character and numeric data. The date data type consists of two principal elements: date and time. It's important to keep in mind that the date data type includes time when comparing two dates with each other for equality.

The default date format in many Oracle databases is DD-MON-YY, where DD represents the day, MON is the month and YY is the two-digit year. A date can be inserted into a table without specifying either the four-digit year or a value for the time element. Oracle will default the century to '20' for years '00–49' and '19' for years '50–99'. Without a specific time being specified during an insert, the time will default to midnight, which is represented as '00:00:00'.

Date Functions

As with the numeric and character data types, Oracle has provided many date functions to help with the manipulation of date data. If you were to routinely print customized letters to your best customers offering them a special deal that expires on the last day of the month, the last_day function could be used to automatically generate the expiration date for the offer. Table 2-6 shows the commonest date functions.

Function	Action	Example	Displays
Sysdate	Returns current system date. Time could also be retrieved using the to_char function, which is discussed in the next section.	select sysdate from dual;	17-MAR-04 on March 17, 2004
last_day(date)	Returns last day of the month for date.	select last_day('17-MAR-04') from dual;	31-MAR-04

Team Fly