I HAVE A TABLE WITH, AMONG OTHERS, THE FIELDS DATE1 AND DATE2 IN WHICH
DATA ARE IN THE FORMAT (20070417, ....) (THEY ARE NUMBERS AND NOT DATES!!!!!).
I WANT TO CALCULATE THE DIFFERENCE BETWEEN DATE2 AND DATE1 ACCORDING
TO THE 30/360 DAY COUNT (EVERY MONTH HAS THIRTY DAYS). HOW CAN I
TRANSFORM THE NUMBERS INTO DATES AND THEN CALCULATE THE DIFFERENCE?
PLEASE ANSWER TO ME AS SOON AS POSSIBLE.
THANK YOU IN ADVANCE
ANNA from Verona (Italy)

which database? date functions vary wildly from one to the next

DB2. I use QMF for Windows.

Please help me doing my query.

Anna - Verona Italy

thread moved to DB2 forum

you have to convert integer to date to get yyyy-mm-dd
eg
select date(
substr(char(col1),1,4) ||'-'||substr(char(col1),5,2) ||'-'||substr(char(col1),7,2) ) from tt;
for both columns. dates can be compared/substracted... any operation. see online info center and date comparison

To get the year out of the numeric value you might use the formula
TRUNC(DATE1/10000,0)

the month you get with
TRUNC(MOD(DATE1,10000)/100,0)

and the day you get with
MOD(DATE1,100)


the difference of two dates can be calculated by

difference = ( TRUNC(DATE2/10000,0) - TRUNC(DATE1/10000,0) ) *360 + ( TRUNC(MOD(DATE2,10000)/100,0) - TRUNC(MOD(DATE1,10000)/100,0) ) *30 + ( MOD(DATE2,100) - MOD(DATE1,100) )

Thank you for your reply. I like this formula because it's very short but
unfortunately it doesnt' work. Everything is okay with years and months but
days are not counted properly.
I'd better explain to you how days are counted with 30/360 day count:
each month you count each day as 1 but only up to 30
in february you count each day as 1 and on the 28th (if it's a normal year - 365 days) you count 30; if it's a leap year (366 days) you count 28 on the
28th and you count 30 on the 29th.
Your formula doesn't take into account dates with day 31 or 28 or 29.
(end date day 31 - beg.date day 30) = 1 which is wrong, it should be = zero
(end date day 5 - beg. date day 31) = -26 but it should be -25
I tried to imagine some selections or 'when' clauses that change 31, 29 and
28 (the latter only if it's not a leap year - maybe you can say if 29/2/year doesn't exist) into 30, but I' not very expert at SQL and I'm not able to write
it.
Before knowing that there are differences among SQL's languages depending on the databases used, I asked SQLTEAM.COM for help and someone sent
me this function:
CREATE FUNCTION dbo.fnGiorno360
(
@Date1 INT,
@Date2 INT
)
RETURNS INT
AS
BEGIN
DECLARE @Months INT,
@Days INT,
@dt1 DATETIME,
@dt2 DATETIME

IF @Date1 > @Date2
SELECT @Days = @Date1,
@Date1 = @Date2,
@Date2 = @Days

SELECT @dt1 = CAST(@Date1 AS VARCHAR),
@dt2 = CAST(@Date2 AS VARCHAR),
@Months = DATEDIFF(MONTH, @dt1, @dt2)

IF @Months > 0
SET @Months = @Months - 1

SELECT @Days = 30 - CASE WHEN DATEPART(DAY, @dt1) > 30 THEN 30 ELSE DATEPART(DAY, @dt1) END,
@Days = @Days + CASE WHEN DATEPART(DAY, @dt2) > 30 THEN 30 ELSE DATEPART(DAY, @dt2) END

RETURN @Days + 30 * @Months
END
Then use it with
select dbo.fnGiorno360(20070331, 20070815)

I think he didnt' think about february.
Of course this function wouldn't work with my database because it's for
SQL Server. Moreover, I myself can't insert functions in my queries because
only programming staff can do that and I'm an accountant.

Is there a hope to solve this problem?

I have another question to make: once I have created a new column by
making operations among db2 columns, how can I select my new column to
make other operations? (When I try the answer is that column xxx is not present in my table).

I hope to hear from you soon and thank you in advance.

Anna - from Verona (Italy)

try this to get the desired value for day:

CASE
WHEN MOD(DATE1,1000)= 229 THEN 30
WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE1,100),30)
END

You can use a "nested table" expression.
e.g.:

SELECT (year2 - year1)*360 + (month2 - month1)*30 + (day2 - day1) as difference
FROM
(
SELECT
TRUNC(DATE1/10000,0) as year1 ,
TRUNC(MOD(DATE1,10000)/100,0) as month1 ,
CASE
WHEN MOD(DATE1,1000)= 229 THEN 30
WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE1,100),30)
END as day1,
TRUNC(DATE2/10000,0) as year2 ,
TRUNC(MOD(DATE2,10000)/100,0) as month2 ,
CASE
WHEN MOD(DATE2,1000)= 229 THEN 30
WHEN MOD(DATE2,1000)= 228 AND MOD(TRUNC(DATE2/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE2,100),30)
END as day2
FROM your.table
) correl

Thak you so much. The formula works perfectly, even in leap years!
With the query I'm making (that would be of no use without this formula) I will turn a three-days-per-month work into a five-minutes-per-month work.

Could you explain to me what calculation is made in the expression
MOD(TRUNC(DATE2/10000,0),4)? It seem to consider leap years but how does it do it? I couldn't find anything in my handbook.

Another question:
Can I only use one nested table or can I nest more tables one into another?
i.e. select from xxxxxx
from ( select xxxxx
from (select xxxx
from (select xxxxx
If that's possible, how should I do it? I tried to but unsuccessfully.

Thank you again and congratulations.
Ciao. Anna

If the year is divisible by four without remainder, it is a leap year.

The formula to get the year is:
TRUNC(DATE2/10000,0)

if the remainder of the division by four is not zero
MOD(year,4) > 0
then it is no leap year.

now simply combine the two formulas:
if MOD(TRUNC(DATE2/10000,0),4) > 0
then it is no leap year.


There are up to 16 levels possible
eg:
SELECT * FROM
( SELECT * FROM
( SELECT * FROM
( SELECT * FROM SYSIBM.SYSDUMMY1 ) A
) B
) C

you have to use a correlation character after each nested table expression

I'm having problems with some calculations:

Here is the query:

SELECT

SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
GG_CED_C, GG_MAT_C,
CASE WHEN (CALC = 801 OR CALC = 306 OR CALC = 307 OR CALC = 811
OR CALC = 871) THEN (RATE/NR_CED)/GG_CED_S* GG_MAT_S
WHEN CALC = 315 THEN RATE/GG_CED_S* GG_MAT_S
WHEN CALC = 300 OR CALC = 301 OR CALC = 834 OR CALC = 822
THEN RATE / 360 * GG_MAT_C
WHEN CALC = 303 OR CALC = 304 OR CALC = 835
THEN RATE/ 360 * GG_MAT_S
WHEN CALC = 876 THEN ROUND((RATE/ 360 * GG_MAT_S),2)
WHEN CALC = 877 OR CALC = 325 THEN
ROUND(( RATE/ 360 * GG_MAT_S),3)
WHEN CALC = 878 THEN ROUND((RATE/ 360 * GG_MAT_S),5)
END AS RATEO

FROM (
SELECT SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
GG_CED_S, GG_MAT_S,NR_CED

(YEAR2 - YEAR1)*360 + (MONTH2 - MONTH1)*30 + (DAY2 - DAY1)AS GG_CED_C,
(YEARRIL - YEAR1)*360 + (MONTHRIL - MONTH1)*30 + (DAYRIL - DAY1)AS GG_MAT_C,
FROM (
SELECT
A.SEC, A.VER, A.DES, CURC, CALC, B_DATE, E_DATE, RATE,
PER,
TRUNC(B_DATE/10000,0) AS YEAR1 ,
TRUNC(MOD(B_DATE,10000)/100,0) AS MONTH1 ,
CASE
WHEN MOD(B_DATE,1000)= 229 THEN 30
WHEN MOD(B_DATE,1000)= 228
AND MOD(TRUNC(B_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(B_DATE,100),30)
END AS DAY1,
TRUNC(E_DATE/10000,0) AS YEAR2 ,
TRUNC(MOD(E_DATE,10000)/100,0) AS MONTH2 ,
CASE
WHEN MOD(E_DATE,1000)= 229 THEN 30
WHEN MOD(E_DATE,1000)= 228
AND MOD(TRUNC(E_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(E_DATE,100),30)
END AS DAY2,
TRUNC(&DRIL/10000,0) AS YEARRIL ,
TRUNC(MOD(&DRIL,10000)/100,0) AS MONTHRIL ,
CASE
WHEN MOD(&DRIL,1000)= 229 THEN 30
WHEN MOD(&DRIL,1000)= 228
AND MOD(TRUNC(&DRIL/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(&DRIL,100),30)
END AS DAYRIL,DAYS(CHAR((SUBSTR(CHAR(E_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(E_DATE),7,2)) !! '-' !!
(SUBSTR(CHAR(E_DATE),9,2)))) -
DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '-' !!
(SUBSTR(CHAR(B_DATE),9,2)))) AS GG_CED_S,

DAYS(CHAR(&DATA)) -
DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '-' !!
(SUBSTR(CHAR(B_DATE),9,2)))) AS GG_MAT_S,

CASE WHEN PER = 'T' THEN 4 WHEN PER = 'S' THEN 2 WHEN PER =
'A' THEN 1 END AS NR_CED

FROM TABLEONE A, TABLETWO C


the formulas that create columns with date differences are perfect. The day
count is always correct.
Problems rise when I make the calculation written in blue: if I calculate

RATE/ 360 * GG_MAT_S
or RATE/ GG_CED_C*GG_MAT_C
everything works perfectly.

But when I calculate RATE/360*GG_CED_C it sometimes calculate it and
sometimes the related columns are returned empty.
I can't find anything that distinguish records that are calculated from records
that are not. GG_MAT_C with low values seem to be always calculated but
for examples there are two records with the same dates and in one case the calculation is made and in the other it isn't.
What's the problem? 360 and GG_CED_C doesn't get along together!
Now that I'm writing I'm wondering if maybe I could solve the problem by
creating a new column calculating RATEO/360 AS RAT_360 and then
calculate RAT_360 * GG_MAT_C_. What do you think?

In any case now that I've seen what's happened with this query, I wonder
if SQL IS APT TO MAKE CALCULATIONS (???) After all its duty is to query data and not to make calculations among them.
I'd like to know from you experts if I can consider SQL reliable when it comes
to calculations, because in this case it was easy to spot the failure, but
it might not be so in other cases.

Thank you for your attention.

Anna - Verona (Italy)

I don't know the semantics of your data and the query, but I'd say that the calculations have a serious problem: If MONTH2 is smaller than MONTH1 (say January 2007 vs. April 2006), you will get a negative month count. Same applies do days.

Btw, could you do regular date arithmetics or are you fixed on the 30 days/month rule? Regular date arithmetics would be much, much simpler to write down. For your stuff, I'd also recommend that you write some SQL-bodied functions that encapsulate something like calculating the difference between two dates.

What do you mean with "empty"? You are calculating with numbers, so there is no empty possible. Do you mean NULL? Or is it 0 (zero)?

What do you mean?

Have you veryfied that the GG_CED_C values are correct? Just take the respective subquery and look at its intermediate table.

Also, you should really try to reduce your query to the bare minimum that the problem still occurs but all unnecessary stuff is removed. (Something like 5 lines of SQL code should be sufficient in this case.) Then dig further into the details to understand why things don't work as you hope.

Sure. SQL is a full programming language.

I'm confused. You know what the error is in your query? I thought you don't know what's going on. I think you have a problem in the query itself and that's why the mathematical expressions don't result in what you want to have.
(Your call to decide whether I fall in the "expect category.)

The calculations of date differences are correct. Of course I can have
get a negative month or days count, but it's okay. For example:
01/05/2007 - (minus) 01/03/2008: it's true that I have a negative month
count. But before counting the month I've made the difference between
years, which is (2008 - 2007) * 360: I have counted a whole year from
which I have to subtract two months (the difference between the two date
is 10 months i.e. less than one year.
Day difference columns are perfect! And they were created by making the
calculations you find in the query.
So, now I have column GG_CED_C that was created by using the formula in
red. There are numbers in it, and they are successfully used to made
calculations, in other cases. But when I use 360 (a number I type) in the
calculation with GG_CED_C it can give the right result or it can give no result (if I use 360 with GG_CED_S everything is okay).
Take this example:

20061229 20070630 3,995 180 90 0,99875
20061229 20070630 4,185 180 90
The dates are identical, day differences are identical, but in the first record
I have my count (RATEO), in the second there is nothing.

I reduce my query. For example I took off the first (from the beginning of
the query) 'when clause' and simply told SQL to calculate every record with
/360*GG_CED_C and the result was the same: some records are calculated
and when they are, they are calculated correctly, and some don't.
Now what I think is that since GG_CED_C is created using 360 in the formula,
then the programm gets crazy when it finds another 360. But what I don't
understand is why it behaves in different ways. With some records it makes
the calculation, for others it doesn't.

With 'spotting the failure' I meant that I could realize that there was
something wrong. Not that I understood WHAT was wrong.

Of course I think that you are an expert. The fact is that in my post I put
a very long query and I knew that it would be difficult to find the patience
to read it carefully.
As for Valentino Rossi, of course he had problems with his tyres, but I was
referring to the mistake he made in the bend a few minutes after starting.
I don't think it was due to tyres. It was his fault.
By the way, who's your favourite rider? And what's your favourite motorcycle
brand?
Could you recommend me some good SQL book? Not in German!
Thank you again.

Anna

Yes, you're right there.

What's your environment that executes the query and shows the result? Again, there are no "empty" values for numbers in DB2. It is probably a NULL or 0 (zero) and your application represent it as this empty thing. You should execute the query on the command line (or in the Command Editor). That way you could more easily drill down into the query.

You should identify 1 record where it fails, 1 where it works, and 1 where it fails partly. Then do through the expressions and figure out what the results are at each step for all of the 3 records.