Thanks, your explanation helped me a lot.

Roberto Carlos



Em 10/11/2008 21:17, Helen Borrie   escreveu:

At 09:21 AM 11/11/2008, you wrote:

>I am trying to calculate worked hours for payment just like Excel does when a cell is formated as [HH:mm:ss].

Spreadsheets do formatting, databases don't. If you want formatted output, you need to do this either at the server or in your application.

You need two TIMESTAMP columns, for clock-on and clock-off date and time. Don't try to work with a DATE and some TIME variables. Only a TIMESTAMP "knows" about times within its own day.

Your routine will subtract clock_on from clock_off, returning a decimal(18,9). That is the result you will have to work with. It is in days. If you need to *operate* on this value, multiply it by 24 to get the hours. The result will also be (18,9).
You can then work on the integral part of this number for the hours...and then proceed to work on the decimal part: multiply it by 60 to get the minutes from the integral part of the result; then multiply the decimal part by 60 to get the seconds from the integral part of that result. Format the pieces into a VARCHAR(8).

If you can't find a UDF that does this, the simplest way to achieve it is to add a VARCHAR(8) column to the table and write a little generic SP to fill it, that can be called by an Insert or Update trigger whenever the CLOCKED_OFF value changes (from NULL to a value or from one value to another). You should also have a CHECK constraint on CLOCKED_OFF to ensure that it is greater than or equal to CLOCKED_ON.

./heLen


[Non-text portions of this message have been removed]