# Thread: Calculating the fractional part of a day

1. Registered User
Join Date
Mar 2012
Posts
16

## Unanswered: Calculating the fractional part of a day

Hi,

I need to create a UDF that returns a fraction of a day. I pass a parameter in the format hhmmss, where h represents hours, mm represents minutes, ss represents seconds, then 360000 would correspond to 36 hours, 00 minutes and 00 seconds. The return for this value would be 1.5 days, however only returns the integer part.

Code:
```CREATE FUNCTION "GNR"."F_FRACDAY" ( TIMEFORMATVALUE INTEGER )
RETURNS DECIMAL
NO EXTERNAL ACTION

F1: BEGIN ATOMIC

DECLARE HS DECIMAL;
DECLARE MS DECIMAL;
DECLARE SCS DECIMAL;
DECLARE TOTAL DECIMAL;

DECLARE TIMESTR VARCHAR(6);

SET HS = DECIMAL(SUBSTR(TIMESTR,1,2));
SET MS = DECIMAL(SUBSTR(TIMESTR,3,2));
SET SCS = DECIMAL(SUBSTR(TIMESTR,5,2));

SET TOTAL = (HS/24) + (MS/1440) + (SCS/84000);

END```

2. :-)
Join Date
Jun 2003
Location
Posts
5,516
If you declare your variables with 0 scale, how do you expect to get fractional numbers?

3. Registered User
Join Date
Mar 2012
Posts
16
Thanks!!!!!!!!

Originally Posted by n_i
If you declare your variables with 0 scale, how do you expect to get fractional numbers?

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
The common programming practices(SQL wouldn't be an exception.)
which were related to your function-body would be...
(1) Don't declare unnecesary intermediate result variables, if it was used once.
- all variables are useless.

(2) Utilize languages capabilities related to each datatype.
- use compound mathematical expressions, considering priority of operators.
- use builtin-functions in DB2 SQL.
- utilize the difference of handling of fractions between integer datatype and decimal datatype.
- so on...

Note: I learned these general practices(and more) in my early study of Assembler and PL/I languages.

As a result, an example of revised function-body might be
Code:
```CREATE FUNCTION GNR.F_FRACDAY( time_format INTEGER )
RETURNS DECIMAL(9 , 6)
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
time_format          / 10000 /    24.
+ MOD(time_format , 10000) /   100 /  1440.
+ MOD(time_format ,   100)         / 84000.
;```
Last edited by tonkuma; 03-25-12 at 15:02.

5. Registered User
Join Date
Mar 2012
Posts
16
Thanks for your recommendations. I'll try to apply it.
Now I have another problem. Can I use a UDF in a group by clause?

I am using the following statement:

Code:
```select
COUNT(*) as qtd,
GNR.F_DATEDIFF2(TIMESTAMP1,TIMESTAMP2) F_DATEDIFF2
from
GNR.TABLE1 T
group by
GNR.F_DATEDIFF2(TIMESTAMP1,TIMESTAMP2)```
My udf:

Code:
```CREATE FUNCTION GNR.F_DATEDIFF2(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS DECIMAL(20,10)

RETURN
(
DECIMAL(( DAYS(t1) - DAYS(t2)))  +
DECIMAL(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))/(86400.)
)```
Thanks.
Davis

Originally Posted by tonkuma
The common programming practices(SQL wouldn't be an exception.)
which were related to your function-body would be...
(1) Don't declare unnecesary intermediate result variables, if it was used once.
- all variables are useless.

(2) Utilize languages capabilities related to each datatype.
- use compound mathematical expressions, considering priority of operators.
- use builtin-functions in DB2 SQL.
- utilize the difference of handling of fractions between integer datatype and decimal datatype.
- so on...

Note: I learned these general practices(and more) in my early study of Assembler and PL/I languages.

As a result, an example of revised function-body might be
Code:
```CREATE FUNCTION GNR.F_FRACDAY( time_format INTEGER )
RETURNS DECIMAL(9 , 6)
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
time_format          / 10000 /    24.
+ MOD(time_format , 10000) /   100 /  1440.
+ MOD(time_format ,   100)         / 84000.
;```

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Can I use a UDF in a group by clause?
Yes You Can!

But, it should be DETERMINISTIC and NO EXTERNAL ACTION.(Defaults are NOT DETERMINISTIC and EXTERNAL ACTION.)
Please see my example of "CREATE FUNCTION GNR.F_FRACDAY".

Here is more detailed descriptions.
IBM DB2 9.7 Information Center for Linux, UNIX, and Windows
group-by-clause

Code:
```             .-,-----------------------.
V                         |
>>-GROUP BY----+-grouping-expression-+-+-----------------------><
+-grouping-sets-------+
'-super-groups--------'```
The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R.
R is the result of the previous clause of the subselect.

In its simplest form, a GROUP BY clause contains a grouping expression.
A grouping expression is an expression used in defining the grouping of R.
Each expression or column name included in grouping-expression must unambiguously identify a column of R (SQLSTATE 42702 or 42703).
A grouping expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822),
or any expression or function that is not deterministic or has an external action (SQLSTATE 42845).
Last edited by tonkuma; 03-26-12 at 00:59.

7. Registered User
Join Date
Mar 2012
Posts
16
Thank you again.

Originally Posted by tonkuma
Yes You Can!

But, it should be DETERMINISTIC and NO EXTERNAL ACTION.(Defaults are NOT DETERMINISTIC and EXTERNAL ACTION.)
Please see my example of "CREATE FUNCTION GNR.F_FRACDAY".

Here is more detailed descriptions.
IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•