Recipe8.2.Determining the Number of Days Between Two Dates


Recipe 8.2. Determining the Number of Days Between Two Dates

Problem

You want to find the difference between two dates and represent the result in days. For example, you want to find the difference in days between the HIREDATEs of employee ALLEN and employee WARD.

Solution

DB2

Use two inline views to find the HIREDATEs for WARD and ALLEN. Then subtract one HIREDATE from the other using the DAYS function:

  1 select days(ward_hd) - days(allen_hd)  2   from (  3 select hiredate as ward_hd  4   from emp  5  where ename = 'WARD'  6        ) x,  7        (  8 select hiredate as allen_hd  9   from emp 10  where ename = 'ALLEN' 11        ) y 

Oracle and PostgreSQL

Use two inline views to find the HIREDATEs for WARD and ALLEN, and then subtract one date from the other:

  1 select ward_hd - allen_hd  2   from (  3 select hiredate as ward_hd  4   from emp  5  where ename = 'WARD'  6        ) x,  7        (  8 select hiredate as allen_hd  9   from emp 10  where ename = 'ALLEN' 11        ) y 

MySQL and SQL Server

Use the function DATEDIFF to find the number of days between two dates. MySQL's version of DATEDIFF requires only two parameters (the two dates you want to find the difference in days between), and the smaller of the two dates should be passed first to avoid negative values (opposite in SQL Server). SQL Server's version of the function allows you to specify what you want the return value to represent (in this example you want to return the difference in days). The solution following uses the SQL Server version:

  1 select datediff(day,allen_hd,ward_hd)  2   from (  3 select hiredate as ward_hd  4   from emp  5  where ename = 'WARD'  6        ) x,  7        (  8 select hiredate as allen_hd  9   from emp 10  where ename = 'ALLEN' 11        ) y 

MySQL users can simply remove the first argument of the function and flip-flop the order in which ALLEN_HD and WARD_HD is passed.

Discussion

For all solutions, inline views X and Y return the HIREDATEs for employees WARD and ALLEN respectively. For example:

  select ward_hd, allen_hd     from ( select hiredate as ward_hd   from emp  where ename = 'WARD'        ) y,        ( select hiredate as allen_hd   from emp  where ename = 'ALLEN'        ) x WARD_HD     ALLEN_HD ----------- ---------- 22-FEB-1981 20-FEB-1981 

You'll notice a Cartesian product is created, because there is no join specified between X and Y. In this case, the lack of a join is harmless as the cardinalities for X and Y are both 1, thus the result set will ultimately have one row (obviously, because 1x1=1). To get the difference in days, simply subtract one of the two values returned from the other using methods appropriate for your database.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net