Because your TOOTATUD_* parameters are INTEGER,
you are rounding everything to whole days.


Also, because you are not selecting anything from rdb$database here

> CAST( ( extract(day from :alates)||'.'||extract(month from
> :alates)||'.'||extract(year from :alates) ||' 6:00:00' ) as date)
> from rdb$database into :paevane_algus;

you should use regular assignment statement instead

paevane_algus = CAST(...);


Ivan

----- Original Message -----
From: "Raigo" <raigo77@...>
To: <firebird-support@yahoogroups.com>
Sent: Sunday, September 19, 2004 9:39 PM
Subject: Re: [firebird-support] Re: Adding time to date


> I created a Stored Procedure to calculate how many hours was during day
> (6:00-18:00), evening (18:00-22:00) and night (22:00-6:00 actually:
> 0:00-6:00 and 22:00-24:00).
> this is meant to be used with view v_toopaev from my previous post:
> BEGIN(algus) and END(lopp) are on the same date.
>
> Problem is that this procedure returns 0 (minutes) for all of those.
> However same logic used in Delphi procedure works fine.
> What may be wrong here? (variable names are in Estonian, comment is the
> meaning in Eglish)
>
>
> CREATE PROCEDURE SP_TOOPAEV_TUNDE (
> ALATES TIMESTAMP, /* beginning time*/
> KUNI TIMESTAMP /* end time*/
> ) RETURNS (
> TOOTATUD_OOSEL INTEGER, /* WORKED DURING NIGHT */
> TOOTATUD_PAEVAL INTEGER, /* WORKED DURING DAY */
> TOOTATUD_OHTUL INTEGER /* WORKED DURING EVENING */
> ) AS
>
> DECLARE VARIABLE paevane_algus Date; /* daily hour start */
> DECLARE VARIABLE ohtune_algus Date; /* evening hour start */
> DECLARE VARIABLE oine_algus Date; /* nightly hour start */
> DECLARE VARIABLE keskoo Date; /* midnight */
>
> BEGIN
>
>
> /* daily hour start */
> select
> CAST( ( extract(day from :alates)||'.'||extract(month from
> :alates)||'.'||extract(year from :alates) ||' 6:00:00' ) as date)
> from rdb$database into :paevane_algus;
>
>
> /* evening hour start */
> select
> CAST( ( extract(day from :alates)||'.'||extract(month from
> :alates)||'.'||extract(year from :alates) ||' 18:00:00' ) as date)
> from rdb$database
> into :ohtune_algus;
>
> /* nightly hour start */
> select
> CAST( ( extract(day from :alates)||'.'||extract(month from
> :alates)||'.'||extract(year from :alates) ||' 22:00:00' ) as date)
> from rdb$database
> into :oine_algus;
>
> /* midnight */
> select
> CAST( ( extract(day from :alates)||'.'||extract(month from
> :alates)||'.'||extract(year from :alates) ) as date)+1 from rdb$database
> into :keskoo;
>
> /* nightly hours in the morning */
> if (alates<=paevane_algus) then
> begin
> if (kuni>=paevane_algus) then
> tootatud_oosel=paevane_algus-alates;
>
> if (kuni<paevane_algus) then
> tootatud_oosel=kuni-alates;
>
> end
>
>
> /* nightly hours in the evening */
> if (kuni<=keskoo) then
> begin
> if (alates>=oine_algus) then
> tootatud_oosel=tootatud_oosel+(kuni-alates);
>
> if (alates<oine_algus) then
> tootatud_oosel=tootatud_oosel+(kuni-oine_algus);
> end
>
> if (kuni>oine_algus) then
> begin
> if (alates>=oine_algus) then
> tootatud_oosel=tootatud_oosel+(keskoo-alates);
> if (alates<oine_algus) then
> tootatud_oosel=tootatud_oosel+(keskoo-oine_algus);
> end
>
> tootatud_oosel=tootatud_oosel*24*60;
>
>
> /* hours during the day */
> if (kuni<=ohtune_algus) then
> begin
> if (alates>=paevane_algus) then
> tootatud_paeval=kuni-alates;
> if (alates<paevane_algus) then
> tootatud_paeval=kuni-paevane_algus;
> end
>
> if (kuni>ohtune_algus) then
> begin
> if (alates>=paevane_algus) then
> tootatud_paeval=ohtune_algus-alates;
> if (alates<paevane_algus) then
> tootatud_paeval=ohtune_algus-paevane_algus;
> end
> tootatud_paeval=tootatud_paeval*24*60;
>
> /* hours during evening */
>
> if (kuni<=oine_algus) then
> begin
> if (alates>=ohtune_algus) then
> tootatud_ohtul=kuni-alates;
> if (alates<ohtune_algus) then
> tootatud_ohtul=kuni-ohtune_algus;
> end
>
> if (kuni>oine_algus) then
> begin
> if (alates>=ohtune_algus) then
> tootatud_ohtul=oine_algus-alates;
> if (alates<ohtune_algus) then
> tootatud_ohtul=oine_algus-ohtune_algus;
> end
> tootatud_ohtul=tootatud_ohtul*24*60;
>
>
> suspend;
> END