Re: Calculate diffrence between dates - weeks - but not so simple ...

On 4/7/11 8:58 AM, Tomasz Skorża wrote:

I need to create an algorithm which allow me to calculate difference
between two dates but assumption is that first day of week is
SATURDAY, and week going from Saturday to Friday.

So if first date is 2011-03-28 - Monday, then for second date from
2011-03-29 to 2011-04-01 algorithm should return 1 (week), but for
2011-04-02 - Saturday, algorithm should return 2 (weeks).
Have you any idea? <<SNIP>>

The following might properly effect the desired?:

<code>

Exec SQL
-- int( abs( ( days( bow(t) ) - days( bow(f) ) ) / 7 + 1 )
-- bow(d) to adjust date d to beginning [Saturday] of the week.
-- Get difference in days of the two adjusted dates.
-- Divide by seven to get number of weeks difference. Add one
-- since diff is zero for two dates in same adjusted "week", to
-- reflect stated requirement; absolute value for T before F.
VALUES int( abs( days( :t - case when dayofweek(:t)=7 then 0
else dayofweek(:t) end days )
- days( :f - case when dayofweek(:f)=7 then 0
else dayofweek(:f) end days )
) / 7 + 1 ) INTO :r
;

</code>

Regards, Chuck