Database systems treat dates and times in many different ways. Fortunately, Oracle provides a special datatype--;DATE--;for dealing with dates and times. All you need to know about the internal format of this datatype is that stores the century, year, month, day, hour, minute, and second. As this chapter points out, using the DATE datatype where appropriate has many advantages.
Oracle's DATE datatype is efficient because it requires only seven bytes of storage. In addition, when you define a column with the DATE datatype, you can use all of Oracle's built-in date and time manipulation functions.
The DATE datatype is also extremely convenient for the application developer. One can argue about whether or not the algorithms used by Oracle are optimal; you may feel that other methods are more efficient for storing date and time values. But without question, using this datatype can save application developers a significant amount of time and effort. In fact, because the advantages of this datatype are so clear, you really should use the DATE datatype whenever you need to store date or time information.
Oracle's DATE datatype also stores time information: hour, minute, and second. You can use a column defined as DATE to store only date information, only time information, or both.
If you choose not to use the DATE datatype for storing date and time information, you will be forced to use other algorithms for manipulating the formats you have defined because you won't be able to use any of Oracle's built-in date and time functions. A task that could have been accomplished in a single SELECT statement will require additional processing in a programming language or development environment. The following scenario shows the consequences of not using Oracle's DATE datatype in an appropriate situation. The example uses a database for a consumer electronics repair store, Frayed Wires.
Suppose that you need to store the hire date and a possible termination date for each employee. If you made an incorrect decision and decided to store the employee's hire date as a numeric value in the format YYMMDD, the hire date for an employee hired on May 9, 1957, would be stored as 570509. Here's what the table would look like:
Employee_ID NUMBER Last_Name VARCHAR2(30) First_Name VARCHAR2(15) Position VARCHAR2(30) Hire_Date NUMBER(6)
Cleverly (or so you thought), you decided to use a format of YYMMDD so that you could order the hire date in either ascending order or descending order. You could then use the following SELECT statement to retrieve employee information ordered by hire date:
select Last_Name, First_Name, Hire_Date from Employee Order by Hire_Date
But this approach has a few problems, namely:
Using Oracle's DATE datatype instead helps to ensure that any application you develop will be portable to other platforms.
Date formats are supported on every platform on which Oracle runs. If you are planning to run an application on a variety of operating systems, you'll find that using the DATE datatype is easier than trying to support a variety of date and time formats for each operating system.
Users frequently need to retrieve records within a particular date range, a task that is easy to accomplish with SQL. For example, you might want to know the names of all the customers who brought in equipment for repair between January 20 and February 14. Because this information is stored in the Repair Header table, one possible SELECT statement to retrieve the desired rows is the following:
select Last_Name, First_Name, Creation_Date from Repair_Header where Creation_Date >= '20-JAN-95' and Creation_Date <= '14-FEB-95'
Another way to retrieve the same information uses the BETWEEN operator.
select Last_Name, First_Name from Repair_Header where Creation_Date between '20-JAN-95' and '14-FEB-95'
Oracle has a number of values called pseudocolumns that can be referenced in SQL statements. One of these values is SYSDATE. Despite its name, SYSDATE also contains time information. Like the DATE datatype, SYSDATE is accurate to the nearest second. SYSDATE is an extremely useful construct for time-stamping rows during an insert or update operation. Many of the examples in this chapter use SYSDATE.
Because the DATE datatype stores the values for century, year, day, month, hour, minute, and second, you can extract and format each value independently. You can also specify whether to abbreviate or fully spell out the date and time elements.
In addition, you can repeat a date or time element in different formats. For instance, you can retrieve the month as a two-digit value and as a fully spelled-out name.
SQL> select Last_Name, First_Name, to_char(Hire_Date,'MM MONTH') month 2 from Employee 3 order by Hire_Date; LAST_NAME FIRST_NAME MONTH ------------------------------ -------------------- --------------- SMITH JEAN 04 APRIL HERNANDEZ RANDY 11 NOVEMBER GLEASON PAUL 04 APRIL BARRETT SARAH 01 JANUARY HIGGINS BEN 02 FEBRUARY YEN CINDY 06 JUNE GILROY MAX 09 SEPTEMBER CARSON BETH 12 DECEMBER SWANSON HARRY 05 MAY 9 rows selected.
TipYou can use SYSDATE and the DUAL table to experiment with various date and time formats. You can select SYSDATE from the DUAL table, but don't insert any rows into the DUAL table--;it must have only one row for some Oracle tools to work correctly. For more information on the DUAL table, please refer to the Note in Chapter 16, "Views, Sequences, and Synonyms."
Table 11.1 lists the valid date format elements.
| Table 11.1. List of date format elements. | ||
| Format | Element Description | Range |
| SS | Second | 0--59 |
| SSSSS | Seconds past midnight | 0--86399 |
| MI | Minute | 0--59 |
| HH | Hour | 0--12 |
| HH24 | Military hour | 0--23 |
| DD | Day of the month | 1--31 (depends on month) |
| DAY | Day of the week | SUNDAY--SATURDAY |
| D | Day of the week | 1--7 |
| DDD | Day of the year | 1--366 (depends on year) |
| MM | Month number | 1--12 |
| MON | Abbreviated month | JAN--DEC |
| MONTH | Last two digits of year | JANUARY--DECEMBER |
| YY | e.g., | 97 |
| YYYY | Full year value | e.g., 1997 |
| YEAR | Year fully spelled | |
| CC | Century | e.g., 19 |
| Q | Quarter | 1--4 |
| J | Julian day | e.g., 2448000 |
| W | Week of the month | 1--5 |
| WW | Week of the year | 1--52 |
Defining a column using the DATE datatype is quite simple. You don't need to specify a format model for a column during table creation or modification. Here is an example of a CREATE TABLE statement that contains DATE datatype columns:
SQL> create table Employee ( 2 employee_id number(4) not null, 3 last_name varchar2(30) not null, 4 first_name varchar2(30) not null, 5 middle_initial char(1), 6 hire_date date not null, 7 termination_date date, 8 date_of_birth date not null); Table created.
The default date format is DD-MON-YY. For instance, 01-JAN-95 is a date in accordance with Oracle's default date format. You can specify dates with this format model without using any other functions or datatype conversion. But if you need to display or specify dates in a different format, then you'll need to use a built-in function to specify the format model you want to use.
If you try to assign a string that doesn't adhere to this default format to a
DATE column, Oracle will probably return an error. For example, if the first two
digits are greater than 31, Oracle will always return the error code ORA-01847.
If the abbreviation for the month is not JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG,
SEP, OCT, NOV, or DEC, Oracle will return error code ORA-01843. If the day
of the month is not within the valid range for that particular month, Oracle will
return error code ORA-01839. Table 11.2 contains a list of Oracle error
codes related to the manipulation of date values.
| Table 11.2. Oracle error codes related to dates. | |
| Oracle Error Code | Description |
| ORA-01847 | Day of month must be between 1 and last day of month |
| ORA-01813 | Hour may be specified once |
| ORA-01839 | Date not valid for month specified |
NoteWe are rapidly approaching a new millennium. Because the beginning years of the 21st century will be in the same range as the days in a month, there will no doubt be some confusion between the year and the day of the month. For example, if you want to assign the date January 2, 2003, you could very easily switch the digits and enter 03-JAN-02 instead of 02-JAN-03. In either case, Oracle would accept the date as valid according to the date format.
You need to remember that a date column value remains a date until you convert it to some other datatype. If, for example, you want to extract the first character of a date column value, you'll need to convert the value to a string using a built-in function named TO_CHAR. The format for this function is
TO_CHAR(date_value,date_format)
where date_value is a literal date value, a date value from a column, or a date value returned by a built-in function, and date_format is a valid Oracle date format.
For example, the following query uses the TO_CHAR function to return the employee hire date using the format MONTH DD, YYYY:
SQL> select Last_Name, First_Name, 2 to_char(Hire_Date,'MONTH DD, YYYY') H_DATE 3 from Employee 4 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE ------------------------------ -------------------- -------------------- SMITH JEAN APRIL 10, 1982 HERNANDEZ RANDY NOVEMBER 18, 1983 GLEASON PAUL APRIL 05, 1984 BARRETT SARAH JANUARY 16, 1989 HIGGINS BEN FEBRUARY 11, 1989 YEN CINDY JUNE 09, 1991 GILROY MAX SEPTEMBER 22, 1992 CARSON BETH DECEMBER 12, 1992 SWANSON HARRY MAY 18, 1993 9 rows selected.
Once a date value has been converted to a string with the TO_CHAR function, you can use it as an argument in other string functions. For example, you can use the function SUBSTR to extract a substring from a string. In the next example the SUBSTR function extracts the first letter of the employee's month of hire.
SQL> select Last_Name, First_Name, 2 substr(to_char(Hire_Date,'MON'),1,1) the_first_letter_of_the_month 3 from Employee 4 order by the_first_letter_of_the_month; LAST_NAME FIRST_NAME T ------------------------------ -------------------- - SMITH JEAN A GLEASON PAUL A CARSON BETH D HIGGINS BEN F BARRETT SARAH J YEN CINDY J SWANSON HARRY M HERNANDEZ RANDY N GILROY MAX S 9 rows selected.
The next section looks at some of the many ways in which dates and times can be displayed. You have a tremendous amount of flexibility in how you display and specify these values.
As you look at the many different formats that you can use in the date format model, remember that each format model must be enclosed in single quotes within the TO_CHAR function. Here's how you can display date values in some commonly used formats.
To display values using the MM/DD/YY format, use the following example:
SQL> select Last_Name, First_Name, to_char(Hire_Date,'MM/DD/YY') H_Date 2 from Employee 3 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE ------------------------------ -------------------- -------------------- SMITH JEAN 04/10/82 HERNANDEZ RANDY 11/18/83 GLEASON PAUL 04/05/84 BARRETT SARAH 01/16/89 HIGGINS BEN 02/11/89 YEN CINDY 06/09/91 GILROY MAX 09/22/92 CARSON BETH 12/12/92 SWANSON HARRY 05/18/93 9 rows selected.
Realize that even if you choose to only display the last two digits of the year, the date value still contains the full year.
You can specify the date format model to display the day of the week fully spelled, as in this example:
SQL> select Last_Name, First_Name, 2 to_char(Hire_Date,'DAY, MON YEAR') H_Date 3 from Employee 4 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE --------------- --------------- --------------------------------------- SMITH JEAN SATURDAY , APR NINETEEN EIGHTY-TWO HERNANDEZ RANDY FRIDAY , NOV NINETEEN EIGHTY-THREE GLEASON PAUL THURSDAY , APR NINETEEN EIGHTY-FOUR BARRETT SARAH MONDAY , JAN NINETEEN EIGHTY-NINE HIGGINS BEN SATURDAY , FEB NINETEEN EIGHTY-NINE YEN CINDY SUNDAY , JUN NINETEEN NINETY-ONE GILROY MAX TUESDAY , SEP NINETEEN NINETY-TWO CARSON BETH SATURDAY , DEC NINETEEN NINETY-TWO SWANSON HARRY TUESDAY , MAY NINETEEN NINETY-THREE 9 rows selected.
To display the ordinal value for the day of the month, add the characters th after the D. In the following example, you can see that the Oracle date format uses the proper characters for the ordinal value.
SQL> select Last_Name, First_Name, 2 to_char(Hire_Date,'MONTH Dth, YYYY') H_Date 3 from Employee 4 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE --------------- --------------- ---------------------------------------- SMITH JEAN APRIL 7th, 1982 HERNANDEZ RANDY NOVEMBER 6th, 1983 GLEASON PAUL APRIL 5th, 1984 BARRETT SARAH JANUARY 2nd, 1989 HIGGINS BEN FEBRUARY 7th, 1989 YEN CINDY JUNE 1st, 1991 GILROY MAX SEPTEMBER 3rd, 1992 CARSON BETH DECEMBER 7th, 1992 SWANSON HARRY MAY 3rd, 1993 9 rows selected.
To display the quarter in which the date value falls, Oracle uses 1 for January through March, 2 for April through June, 3 for July through September, and 4 for October through December. Notice how a string such as QTR can be embedded in the returned value by enclosing it in double quotes.
SQL> select Last_Name, First_Name, 2 to_char(Hire_Date,'"QTR" Q, YY') H_Date 3 from Employee 4 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE --------------- --------------- ------------------------------ SMITH JEAN QTR 2, 82 HERNANDEZ RANDY QTR 4, 83 GLEASON PAUL QTR 2, 84 BARRETT SARAH QTR 1, 89 HIGGINS BEN QTR 1, 89 YEN CINDY QTR 2, 91 GILROY MAX QTR 3, 92 CARSON BETH QTR 4, 92 SWANSON HARRY QTR 2, 93 9 rows selected.
To retrieve the month spelled out fully, use MONTH in the format model. In a previous example, notice that the name of the month is a maximum of nine characters (SEPTEMBER) and that some months will have trailing blanks so that the returned value is always nine characters in length.
The next example demonstrates that the elements of the date format model can be combined in unusual ways. The following example displays the month as a two-digit value with the day of the week and the year fully spelled out.
SQL> select Last_Name, First_Name, 2 to_char(Hire_Date,'MM DAY, YEAR') H_Date 3 from Employee 4 order by Hire_Date; LAST_NAME FIRST_NAME H_DATE --------------- --------------- ---------------------------------------- SMITH JEAN 04 SATURDAY , NINETEEN EIGHTY-TWO HERNANDEZ RANDY 11 FRIDAY , NINETEEN EIGHTY-THREE GLEASON PAUL 04 THURSDAY , NINETEEN EIGHTY-FOUR BARRETT SARAH 01 MONDAY , NINETEEN EIGHTY-NINE HIGGINS BEN 02 SATURDAY , NINETEEN EIGHTY-NINE YEN CINDY 06 SUNDAY , NINETEEN NINETY-ONE GILROY MAX 09 TUESDAY , NINETEEN NINETY-TWO CARSON BETH 12 SATURDAY , NINETEEN NINETY-TWO SWANSON HARRY 05 TUESDAY , NINETEEN NINETY-THREE 9 rows selected.
Not surprisingly, the conversion of string values to dates is similar to the conversion of dates to strings. Instead of using the TO_CHAR built-in function, you use the TO_DATE built-in function because the goal is to specify a date value using a legal date format. The arguments of the TO_DATE function are the reverse of the arguments of the TO_CHAR function.
TO_DATE (string_value, date_format)
For example, if you want to convert a string that doesn't use the Oracle default date format (DD-MON-YY), you would use the TO_DATE function. The following example displays the number of days that have elapsed since the American bicentennial.
SQL> select SYSDATE - TO_DATE('07-04-1976','MM-DD-YYYY')
2 from dual;
SYSDATE-TO_DATE('07-04-1976','MM-DD-YYYY')
------------------------------------------
6878.9465
If you're trying to refer to a date column in an INSERT, UPDATE, or DELETE statement, use the TO_DATE function to convert the string equivalent of the date or time to a valid date format (other than the default date format), as shown here:
update Employee
set Hire_Date = TO_DATE('JAN 18, 1981','MON DD, YYYY')
where
Employee_ID = 1001
Every column defined using the DATE datatype contains both a date and a time value. If you are interested in storing only a time value in this column, the date value will be set to a default value. Here is a demonstration of the use of a common format for displaying times--;HH:MI:SS:
SQL> select Employee_ID, to_char(Time_Clocked_In,'HH:MI:SS') Time_Clocked_In
2 from Time_Clock
3 order by Employee_ID;
EMPLOYEE_ID TIME_CLOCKED_IN
----------- ------------------------------------------------------------------
1002 09:02:03
1003 08:51:12
1004 08:59:33
1005 09:22:12
Remember that the Oracle DATE datatype is capable of storing time to the nearest second.
If you wanted to use a 24-hour time format, the time format should be specified in the following way:
SELECT TO_CHAR(arrival_time,'HH24:MI:SS') FROM DUAL;
NoteIt's very easy to confuse months and minutes in date and time formats. For example, Oracle accepts the following INSERT statement, even though it really isn't what you intended (MM instead of MI):
INSERT INTO EMPLOYEE
(EMPLOYEE_ID, START_TIME)
VALUES
(1033,TO_CHAR('08:05','HH24:MM');
Oracle interprets this statement as follows: The start time for employee number 1033 is set to 8:00 a.m. and the month of May. Because MM is always be between 1 and 12, Oracle always accepts the supplied value, even though it isn't what you intended.
Oracle also has a time format model that enables you to express a time as seconds past midnight. In the following example, assume that the current time is 2:00 a.m.
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL; 7200 which is the equivalent of two hours
By using the time format model SSSSS, Oracle returns the date expressed in seconds past midnight. The time 2:00 a.m. represents two hours past midnight, which is equal to 7,200 seconds.
If you are inserting a new row into a table and specifying a value for a date column using a time-only format model, the date value will be set to the first day of the month and year when the row was inserted.
insert into Customer
(Customer_ID, Last_Name, First_Name, Earliest_Time_to_Call)
values
(2001, 'KNUDSEN', 'MELANIE', to_date('09:00','HH24:MI'))
If you need to keep track of a time to a fraction of a second--;say, milliseconds or microseconds--;it's still a good idea to continue to use Oracle's DATE datatype for storing hours, minutes, and seconds. You can then create a separate column in whatever units are appropriate.
For example, suppose you've been asked to keep track of the finish times for a group of marathon runners at your company. Because this group of runners is incredibly competitive, you need to store the time down to a resolution of 1/1000 of a second. Using the advice given previously, you would add columns to the table for storing the number of milliseconds in the following way:
EMPLOYEE_ID NUMBER LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(20) BEST_TIME DATE BEST_TIME_MSEC NUMBER LATEST_TIME DATE LATEST_TIME_MSEC NUMBER
This table is designed to store both the best marathon time and the latest marathon time for each runner.
By default, if you request a display of the hours, minutes, and seconds for a date column, Oracle will return the hour without indicating whether the hour is a.m. or p.m. If you want to see a.m. or p.m. in dates, you need to append the time format with PM, as shown here:
TO_CHAR(order_date,'HH:MI:SS PM')
If you want to retrieve or specify the hour based upon a 24-hour clock, you must use HH24 in place of HH; for example:
TO_CHAR(order_date,'HH24:MI:SS')
Another advantage of using the Oracle DATE datatype is that it supports date arithmetic. You can add or subtract days from an existing date; for example:
select sysdate + 7 from dual;
By adding 7 to SYSDATE, you can obtain the date a week from the current date. Similarly, you can subtract days from a date value to calculate an earlier date.
The following query will return a list of employee names and their respective termination dates minus one week for those employees who have been terminated; the employees in the list will be ordered in chronological order based on the employee's termination date.
SQL> select Last_Name, First_Name, 2 Termination_Date, Termination_Date - 7 3 from Employee 4 where 5 Termination_Date is not NULL 6 order by Termination_Date; LAST_NAME FIRST_NAME TERMINATI TERMINATI --------------- --------------- --------- --------- HERNANDEZ RANDY 25-NOV-89 18-NOV-89
BARRETT SARAH 04-APR-91 28-MAR-91
To calculate the difference between two dates expressed in days, you simply subtract one date from the other. The following example calculates the age, in days, of employees on their final day of employment.
SQL> select Last_Name, First_Name, 2 Termination_Date - Date_of_Birth Age_in_Days 3 from Employee 4 where 5 Termination_Date is not NULL 6 order by Termination_Date; LAST_NAME FIRST_NAME AGE_IN_DAYS --------------- --------------- ----------- HERNANDEZ RANDY 12524
BARRETT SARAH 10578
Oracle also supplies a built-in function, MONTHS_BETWEEN, that can calculate the difference between two dates in months. The function uses a 31-day month to perform the calculation and will return a fractional portion that corresponds to the fractional portion of a month, if applicable.
In conjunction with the built-in function MONTHS_BETWEEN, you can use the function ADD_MONTHS to add months to a date value. In the following example the MONTHS_BETWEEN function returns an employee's age, in months, as of the hiring date and assigns the value to the alias Age_When_Hired. The last line orders the rows that are returned by the alias Age_When_Hired.
select months_between(Hire_Date,Date_of_Birth) Age_When_Hired from Employee order by Age_When_Hired
The MONTHS_BETWEEN function will return a fractional portion if the difference between the dates is not evenly divisible by 31.
Retrieving records in chronological order is straightforward. Using the same example, imagine that you want to retrieve the list of customers according to the date when the record was created. The SELECT statement is
select Last_Name, First_Name from Customer order by Creation_Date
Oracle will place the customer records in chronological order based on the date and time when the record was created. Customers who were entered into the system on the same date will be sorted using the time portion of Creation_Date.
You can also sort these records in reverse chronological order. For example:
select Last_Name, First_Name from Customer order by Creation_Date DESC
where DESC represents descending order. Oracle assumes ascending order as the default for any columns listed in the order clause. However, you can specify ascending order with the abbreviation ASC, if you think that it helps to clarify the SQL syntax.
select Last_Name, First_Name from Customer order by Creation_Date ASC
Depending on your application requirements, you may need an easy way to convert a time from one time zone to another. Fortunately, Oracle provides a built-in function, NEW_TIME, for this purpose.
NEW_TIME requires three arguments:
NEW_TIME will return the time (and possibly date) in the specified time zone.
The next example again relies on the repair store sample application. Jim Helmholtz, owner of Frayed Wires, is very cautious in his dealings with customers. As you may recall, Jim has instructed you to provide a column in the Customer table to keep track of the earliest and latest times of the day to contact a customer regarding a repair. However, because Jim's store is located in wacky Venice, California, his store is frequented by many out-of-towners, some of whom rely on Frayed Wires to have their consumer electronics repaired. Therefore, Jim also wants to keep track of the time zone of each customer's residence.
One of Jim's customers, Randolph McPherson, lives in a small village on the coast of Newfoundland. On his customer information card, McPherson indicated that he did not want to be bothered with calls before 10:00 a.m. or after 8:00 p.m.--;local time.
Assuming that daylight saving time is not yet in effect, how can you construct a SELECT statement that will provide the earliest and latest times for calling McPherson in Jim's local time zone--;Pacific standard time?
select to_char(new_time(Earliest_Time_to_Call,Time_Zone,'PST'),'HH24:MI'), to_char(new_time(Latest_Time_to_Call,Time_Zone,'PST'),'HH24:MI') from Customer where Last_Name = 'MCPHERSON' 05:30 15:30
With a single SELECT statement, you can convert the earliest and latest times for contacting any customer, regardless of their time zone. Also notice that you can embed the NEW_TIME function within the TO_CHAR function. Table 11.3 contains a list of time zones that work with the NEW_TIME function.
Table 11.3. List of U.S. time zones.
Time Zone Description
AST Atlantic standard time ADT Atlantic daylight saving time BST Bering standard time BDT Bering daylight time CST Central standard time CDT Central daylight saving time EST Eastern standard time EDT Eastern daylight saving time GMT Greenwich mean time HST Alaska-Hawaii standard time HDT Alaska-Hawaii daylight saving time MST Mountain standard time MDT Mountain daylight saving time NST Newfoundland standard time PST Pacific standard time PDT Pacific daylight saving time YST Yukon standard time YDT Yukon daylight saving time
The discussion about time conversion brings up an important question--;is daylight saving time in effect or not? Unfortunately, no single built-in function can help you find the answer. But you can use a series of built-in functions to determine whether or not daylight saving time is in effect.
Daylight saving time goes into effect on the first Sunday in April. Therefore, if the date in question falls during the months of January, February, March, November, or December, daylight saving time is not in effect. If the date in question falls during the months of May, June, July, August, or September, daylight saving time is in effect. If the date in question falls during the month of April, you need to apply the following algorithm:
select to char(to date('01-APR-97'),'D')
from duel
3
if D = 1 then
April 1 falls on a Sunday
else
First Sunday = April 1 + (7 - D + 1) days
7 - D + 1 = 7 - 3 + 1 = 5 day to April 1, 1997
The concept of a Julian day was developed by Joseph Scaliger in 1582. Its purpose was to create a time period of 7,980 years that would simplify the calculation of the number of days between two different dates. Essentially, the Julian day (or number, as it is sometimes called) is the number of days since January 1, 4712 B.C. The Julian day is commonly used by astronomers in a variety of calculations. In our era, the Julian day is just shy of 2.5 million days.
So if you're not Carl Sagan or Stephen Hawking, is there any value in using a Julian day format? The answer is maybe. If you need to calculate the difference between two date and time values to the second, you may want to consider using the Julian day format. For example, to view the current date in the default date format and its equivalent Julian day, use the following statement:
select sysdate, to_char(sysdate,'J') from dual; 15-APR-95 2449823
The next example uses the Julian day date format to calculate the difference between two dates, expressed in seconds. Suppose Jim Helmholtz is becoming rather compulsive. He is no longer interested in knowing only the number of days that each employee has been working at his repair store, Frayed Wires. He now wants you to provide a SELECT statement that will tell him how long a person has been on his payroll to the nearest second!
You need to make these assumptions:
SQL> select Last_Name, First_Name, 2 (to_number(to_char(sysdate,'J')) - 3 to_number(to_char(Hire_Date,'J'))) * 86400 + 4 to_number(to_char(sysdate,'SSSSS')) Length_of_Employment 5 from Employee 6 order by Length_of_Employment; LAST_NAME FIRST_NAME LENGTH_OF_EMPLOYMENT --------------- --------------- -------------------- SWANSON HARRY 61944927 CARSON BETH 75509727 GILROY MAX 82508127 YEN CINDY 123202527 HIGGINS BEN 196469727 BARRETT SARAH 198716127 GLEASON PAUL 349656927 HERNANDEZ RANDY 361666527 SMITH JEAN 412383327 9 rows selected.
This chapter covers the following issues involving date and time values: