Difference between two dates

[pen81]

I am trying to write a query that will calculate the difference between the start and end date of a record, and then sum the results.

The problem is that I want to ignore portions of the periods encountered if the overhang, so for January 06, if a record has start 15/12/05 and end 06/01/06 the answer would be 6 days.

Is there anyway this logic can be built into a query? I have looked at using a case statement but this does not seem appropriate.

 

[TonyGroves]

You can use the DATEDIFF function to get the difference in days between 2 dates. I don't understand what you mean about "overhanging".

 

[pen81]

I need to put logic it to decide what to pass to DATEDIFF. In the above example, I would want to use DATEDIFF(06/01/06, 01/01/06), i.e. substituting the 1st of the month for the actual start date.

The same would have to be done if the end date was greater than 31/01/06.

My problem is incorporating this logic into the query

 

[cLFlaVA]

maybe something like this:

select case when year( start_date ) <> year( end_date )
       then datediff( makedate( year( end_date ), 1 ), end_date ) 
       else datediff( start_date, end_date ) end as date_difference
  from table

*cLFlaVA
----------------------------

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[pen81]

Thanks both for you help, I have a solution.

select
sum(
case when fromdate < '2005-01-01' and todate <= '2005-01-31' then datediff(todate, '2005-01-01')
when fromdate >= '2005-01-01' and todate > '2005-01-31' then datediff('2005-01-31', fromdate)
when fromdate < '2005-01-01' and todate > '2005-01-31' then datediff('2005-01-31', '2005-01-01')
else datediff(todate, fromdate)
end) as 'Days'
from service

 

[cLFlaVA]

hardly a reliable solution, since you are hard-coding dates in there...



*cLFlaVA
----------------------------

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[pen81]

Yes, I was just nailing the logic. January was an example, this code will be slapped in a JSP that will dynamically loop the periods.

Thanks again!

 

[r937]

select sum(
    to_days(least(todate,'2005-01-31'))
   -to_days(greatest('2005-01-01',fromdate))
          ) as 'Days'
  from service

r937.com | rudy.ca