Date Delta SQL – What is Wrong with this SQL Statement?

2 06 2010

June 2, 2010

I found this interesting example a couple of months ago in a book, and something just did not seem right with the example.  What is the problem with the example?

select employee_id, first_name, last_name,
(sysdate - hire_date)*86400 Emp_Length_Seconds,
extract(year from (sysdate - hire_date) year to month) || ' years, ' ||
extract(month from (sysdate - hire_date) year to month) || ' months. '
Emp_Length_Readable
from hr.employees;

I have not used the EXTRACT SQL function much, if at all, prior to seeing this example, but I sensed that something was wrong.

Here is a test script to possibly help you find the problem with the above:

SET PAGESIZE 2000
COLUMN T FORMAT A20
SET TRIMSPOOL ON
SPOOL datedelta.txt

WITH DATES AS (
SELECT
  TO_DATE('01-JAN-2010','DD-MON-YYYY') + (ROWNUM-1) D
FROM
  DUAL
CONNECT BY
  LEVEL<=365)
SELECT
  D1.D D1,
  D2.D D2,
  D2.D-D1.D DATE_DELTA,
  extract(year from (D2.D-D1.D) year to month) || ' years, ' ||
  extract(month from (D2.D-D1.D) year to month) || ' months. ' T
FROM
  DATES D1,
  DATES D2
WHERE
  D1.D<D2.D;

SPOOL OFF

The above script scolls through every start and end date combination for this year, writing a modified version of the calculation from the original SQL statement to a text file.  The output should look something like this:

D1        D2        DATE_DELTA T
--------- --------- ---------- --------------------
01-JAN-10 02-JAN-10          1 0 years, 0 months.
01-JAN-10 03-JAN-10          2 0 years, 0 months.
01-JAN-10 04-JAN-10          3 0 years, 0 months.
01-JAN-10 05-JAN-10          4 0 years, 0 months.
01-JAN-10 06-JAN-10          5 0 years, 0 months.
01-JAN-10 07-JAN-10          6 0 years, 0 months.
01-JAN-10 08-JAN-10          7 0 years, 0 months.
01-JAN-10 09-JAN-10          8 0 years, 0 months.
01-JAN-10 10-JAN-10          9 0 years, 0 months.
...
05-MAR-10 16-JUN-10        103 0 years, 3 months.
05-MAR-10 17-JUN-10        104 0 years, 3 months.
05-MAR-10 18-JUN-10        105 0 years, 3 months.
05-MAR-10 19-JUN-10        106 0 years, 3 months.
05-MAR-10 20-JUN-10        107 0 years, 4 months.
05-MAR-10 21-JUN-10        108 0 years, 4 months.
05-MAR-10 22-JUN-10        109 0 years, 4 months.
...

What is wrong with this SQL statement, and more importantly, how do we fix it?








Follow

Get every new post delivered to your Inbox.

Join 140 other followers