Thursday, 8 March 2012

Business Days between 2 dates in Sybase IQ

This is just ridiculously complex in Sybase compared to the others. But it CAN be done:





-- get the work days between the mondays follwoing each date
abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ), (dateadd(dd,1,dateceiling(wk,dt2)) ))) -  round(abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ) , (dateadd(dd,1,dateceiling(wk,dt2)) )))/7,0)*2 +
--add on the working days to the following monday for the first date
case when (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else  cast(dateformat(dt1,'d') as numeric)-1 end ) < 0 
        then 0 else (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else  cast(dateformat(dt1,'d') as numeric)-1 end ) end -
--take off the working days to the follwoing monday for the second date   
   case when (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else  cast(dateformat(dt2,'d') as numeric)-1  end) < 0 
        then 0 else  (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else  cast(dateformat(dt2,'d') as numeric)-1  end) end biz_days_between


Replace dt1 and dt2 with your dates. Make dt1 < dt2 otherwise I think it will break. I didn't bother testing because coding this took away my will to live.

No comments:

Post a Comment