Ivan thanks.
I was using Delphi's SQL Explorer to play with my queries and SQL
Explorer did not display 00:00 time. after your mail I tried same
queries in IBExpert and I can see 00:00:00 :)

What I need to do is system for employee worktime management.
I have table

CREATE TABLE WORKDAY (
EmplyeeID Emp_id NOT NULL,
Activity_ID VARCHAR(2) NOT NULL,
beginning DATE NOT NULL,
end DATE NOT NULL
);

where Activity_ID are tasks for day (training, piece-work etc.).
BEGINNING is date+time of starting and END is date+time of ending. Trick
is that workday can end on next date. This system is going to be used in
factory what has workshifts (daily, nightly etc.). Work can begin on
evening and end in morning. I need to be able to generate reports from
that how many hours someone worked on certain date. For example
16.09.2004 19:00-24:00 (5 hours)
17.09.2004 00:00-06:00 (6 hours)

I have created 3 views

/* night shift evening hours */
create view v_toopaev_ohtu (fkood, isikid, too_tegevus, algus, lopp)
as
select
fkood,
isikid, /*EmployeeID*/
too_tegevus, /*ActivityID*/
algus, /*BEGINNING*/
CAST(extract(day from lopp)||'.'||extract(month from
lopp)||'.'||extract(year from lopp) as date) as lopp
from toopaev where
CAST(extract(day from algus)||'.'||extract(month from
algus)||'.'||extract(year from algus) as date)<
CAST(extract(day from lopp)||'.'||extract(month from
lopp)||'.'||extract(year from lopp) as date);


/* Workdays that begin and end on same date */
create view v_toopaev_paevane (fkood, isikid, too_tegevus, algus, lopp)
as
select
fkood,
isikid, /*EMPLOYEE ID*/
too_tegevus,
algus, /*BEGINNING*/
lopp /*END*/
from toopaev where
CAST(extract(day from algus)||'.'||extract(month from
algus)||'.'||extract(year from algus) as date)=
CAST(extract(day from lopp)||'.'||extract(month from
lopp)||'.'||extract(year from lopp) as date);


/* night shift morning hours */

create view v_toopaev_hommik (fkood, isikid, too_tegevus, algus, lopp)
as
select
fkood,
isikid,
too_tegevus,
CAST(extract(day from algus)||'.'||extract(month from
algus)||'.'||extract(year from algus) as date)+1 as algus,
lopp
from toopaev where
CAST(extract(day from algus)||'.'||extract(month from
algus)||'.'||extract(year from algus) as date)<
CAST(extract(day from lopp)||'.'||extract(month from
lopp)||'.'||extract(year from lopp) as date);


then I put these views together with UNION select to get emloyee work
for each date:

SELECT fkood,
isikid,
too_tegevus,
algus,
lopp FROM v_toopaev_hommik
UNION
select
fkood,
isikid,
too_tegevus,
algus,
lopp
from v_toopaev_paevane
UNION
select fkood,
isikid,
too_tegevus,
algus,
lopp from v_toopaev_ohtu


I am not sure if this is the best way. If there are better ways I am
more than glad to read (or see) about them.

Raigo




Ivan Prenosil wrote:

>>How to add TIME part do DATE in SELECT query? i am using FB 1.0.3.
>>dialect 1.
>>
>>using just
>>select
>>
>>CAST(extract(day from lopp)||'.'||extract(month from
>>lopp)||'.'||extract(year from lopp) ||' 00:00:00' as date) as lopp
>>from toopaev
>>
>>gives me only date, but does not have time part.
>
>
> What exactly are you doing ? Does youf field already contain
> some time and you want it to be 0:0:0 ?
>
> In dialect 1, the DATE datatype
> is in fact TIMESTAMP, so it *always* contains time.
>
> If you are using ISQL, you have to use command SET TIME ON;
> in order the time part become visible.
>
> Ivan