Hi all.  I'm trying to get the difference between two datetime fields to go into a query
as a single value displayed as a time elapsed, so if TimeIn was 1:00:00 PM and TimeOut was
2:00:00 PM, the result of the subtraction would be displayed as 1:00:00.  I'm interested
in the difference down to the second.  The logic is, a guy punches a timeclock, so you
have TimeIn and TimeOut stored as datetimes in the table.

When I do SELECT TimeOut-TimeIn, I get a value in milliseconds.  I've attempted to use
CAST((timeout-timein),Time) on this expression, but something about my syntax doesn't
work.  I've been searching the boards here and the only applications of this sort were
working with a non-calculated value and when I tried to apply them to this situation, it
appeared to be a no-go.

Or, put another way, if you've got a table you can't change the structure of, and you have
those two fields in it, how would you go about displaying the difference between the two
in a hh:mm:ss kind of format?

Thanks,

Clayton Johnson

Sorry, I was slightly unclear.  TimeIn and TimeOut are datetimes, so in the example they'd
be 2009-07-17 1:00:00 PM and 2009-07-17 2:00:00 PM.

Clayton Johnson

Clayton

You certainly, as you discovered, can't cast the result as a TIME. What you have to do is do the arithmetic and dump the results into a string. Its doable (but horrible) in raw SQL but if you have a more recent DBISAM I'd go for a user defined function.


Roy Lambert [Team Elevate]

"Clayton Johnson" <clayton@bmitech.com> wrote in message
news:98887E50-5F3A-4B5B-919E-E544F794F43B@news.elevatesoft.com...
> Hi all.  I'm trying to get the difference between two datetime fields to
> go into a query
> as a single value displayed as a time elapsed, so if TimeIn was 1:00:00 PM
> and TimeOut was
> 2:00:00 PM, the result of the subtraction would be displayed as 1:00:00.
> I'm interested
> in the difference down to the second.  The logic is, a guy punches a
> timeclock, so you
> have TimeIn and TimeOut stored as datetimes in the table.
>
> When I do SELECT TimeOut-TimeIn, I get a value in milliseconds.  I've
> attempted to use
> CAST((timeout-timein),Time) on this expression, but something about my
> syntax doesn't
> work.  I've been searching the boards here and the only applications of
> this sort were
> working with a non-calculated value and when I tried to apply them to this
> situation, it
> appeared to be a no-go.
>
> Or, put another way, if you've got a table you can't change the structure
> of, and you have
> those two fields in it, how would you go about displaying the difference
> between the two
> in a hh:mm:ss kind of format?
>

If you can live with hours and decimal portion of hours (much easier for
computations, BTW) then something like this should work

select cast(((t2 - t1) / 3600000) as float) as h from table.

Robert

Roy Lambert wrote:

Clayton

You certainly, as you discovered, can't cast the result as a TIME. What you have to do is
do the arithmetic and dump the results into a string. Its doable (but horrible) in raw SQL
but if you have a more recent DBISAM I'd go for a user defined function.


Roy Lambert [Team Elevate]

Hi Roy.  I'm willing to live with horrible.  Do you happen to have an example of how I'd
go about it?  I apologize, but I'm a bit stumped with what the SQL handler here is capable
of in this regard.  Unfortunately, for the application I'm doing here, it needs to be in
hh:mm:ss for display purposes or I'd go with the other suggestion happily.

Clayton Johnson

"Clayton Johnson" <clayton@bmitech.com> wrote in message
news:69CC4B59-6A1A-4A8C-8706-608E9C2D4DD6@news.elevatesoft.com...
> Roy Lambert wrote:
>
> Clayton
>
> You certainly, as you discovered, can't cast the result as a TIME. What
> you have to do is
> do the arithmetic and dump the results into a string. Its doable (but
> horrible) in raw SQL
> but if you have a more recent DBISAM I'd go for a user defined function.
>
>
> Roy Lambert [Team Elevate]
>
> Hi Roy.  I'm willing to live with horrible.  Do you happen to have an
> example of how I'd
> go about it?  I apologize, but I'm a bit stumped with what the SQL handler
> here is capable
> of in this regard.  Unfortunately, for the application I'm doing here, it
> needs to be in
> hh:mm:ss for display purposes or I'd go with the other suggestion happily.
>

Easiest way (not that sophisticated, but much easier to implement) is to add
a calculated string field to the query, and do the conversion in Pascal. In
fact, combining a bit of SQL and a calculated field, it is just a few lines
of code

select
extract (hour from t1) hh1, extract (minute from t1) mm1,
extract (hour from t2) hh2, extract (minute from t2) mm2
from table

OnCalcFields procedure
var hh, mm : integer;
    smm : string;

if mm2 < mm1 then begin
 hh := hh2 - hh1 + 1;
 mm := mm2 + 60 - mm1
end else begin
 hh := hh2 - hh1;
 mm := mm2 - mm1;
end;
smm := inttostr(mm);
while length(smm) < 2 do smm := '0' + smm;
myfield.value := inttostr(hh) + ':' + smm;

Robert

Clayton,

<< Or, put another way, if you've got a table you can't change the structure
of, and you have those two fields in it, how would you go about displaying
the difference between the two in a hh:mm:ss kind of format? >>

Here is the information that you're looking for:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=5&msg=61534&page=1#msg61534

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Clayton,

<< Or, put another way, if you've got a table you can't change the structure
of, and you have those two fields in it, how would you go about displaying
the difference between the two in a hh:mm:ss kind of format? >>

Here is the information that you're looking for:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=5&msg=61534&page=1#msg61534

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for all the help, gentlemen.  I wish I had the option of using the code to do it.
Unfortunately, the pascal aspect of the program is off limits for what I'm doing, all I
have access to is the ability to run queries on the server.  I appreciate your time, I
believe I have a direction to go.  Again I appreciate the help and suggestions.

Clayton Johnson

Clayton,

To get the result with the format h:m:s and if you can have "string" as the
result type, as Roy suggested, then:

SELECT
  CAST(DAYSFROMMSECS(TimeOut - TimeIn)*24 +
       HOURSFROMMSECS(TimeOut -TimeIn) AS VARCHAR(3)) + ':' +
  CAST(MINSFROMMSECS(TimeOut - TimeIn) AS VARCHAR(2)) + ':' +
  CAST(SECSFROMMSECS(TimeOut - TimeIn) AS VARCHAR(2))
FROM
  YourTable


You can also have a TIMESTAMP type result, but only if the interval is <24 :

SELECT
  CAST(
    CAST(DAYSFROMMSECS(TimeOut - TimeIn)*24 +
         HOURSFROMMSECS(TimeOut -TimeIn) AS VARCHAR(3)) + ':' +
    CAST(MINSFROMMSECS(TimeOut - TimeIn) AS VARCHAR(2)) + ':' +
    CAST(SECSFROMMSECS(TimeOut - TimeIn) AS VARCHAR(2))
  AS TIMESTAMP)
FROM
  YourTable


--
Fernando Dias
[Team Elevate]

Clayton

>Hi Roy. I'm willing to live with horrible. Do you happen to have an example of how I'd
>go about it? I apologize, but I'm a bit stumped with what the SQL handler here is capable
>of in this regard. Unfortunately, for the application I'm doing here, it needs to be in
>hh:mm:ss for display purposes or I'd go with the other suggestion happily.

If you have a version of DBISAM that supports UDF's I'd do it that way - write the function in Delphi.

If not what you have to do is build up a piece of code that does the whole job. One of the reasons I say horrible is that you are going to have to repeat calculations and I hate wasting cpu cycles

OK here goes (and I'll explain as I go)


TimeOut-TimeIn produces a number of milliseconds so to go to hours you need

TRUNC((TimeOut-TimeIn)/3600000)

This is an integer and you need a string so

CAST(TRUNC((TimeOut-TimeIn)/3600000) AS VARCHAR(2)

next, since the result might less than double digits you need to add leading zeros and then dump any unnecessary ones so

SUBSTRING('00'+CAST(TRUNC((TimeOut-TimeIn)/3600000) AS VARCHAR(2)),LENGTH('00'+CAST(TRUNC((TimeOut-TimeIn)/3600000) AS VARCHAR(2)))-1,2)

To get minutes it gets a bit more complex since you need the hours calculation in there already

SUBSTRING('00'+CAST(TRUNC((TimeOut-TimeIn)/60000-(TRUNC((TimeOut-TimeIn)/3600000)*60)) AS VARCHAR(2)),LENGTH('00'+CAST(TRUNC((TimeOut-TimeIn)/60000-(TRUNC((TimeOut-TimeIn)/3600000)*60)) AS VARCHAR(2)))-1,2)

and then for seconds you have to work out the hours and minutes as seconds to subtract so you can work out just the seconds (I'll let you do that) and finally stick the lot together with colons between and there you go.

Let us know how it works

Roy Lambert [Team Elevate]