RE: SQL Data Subtraction question

If you subtract 2 dates with SQL the result will be an 8 digit numeric value
representing the difference in Years, months, days. i.e. in your example 103
means 1 Month and 3 Days.
If you want to have the difference in Days, you have to convert both
Timestamps into the calculated number of days since 0001-01-01 by using the
DAYS scalar function and then subtract both number of days:

Example: Days(FirstTimesamp) - Day(SecondTimestamp)

You may also use the TIMESTAMPDIFF function, but it will do some
assumptions, i.e. a month has always 30 days and a year 365 days.

Example for TimestampDiff (calculating the difference in days):
TimestampDiff(16, Cast(FirstTimestamp - SecondTimestamp as Char(22)))

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Evan
Harris
Sent: Wednesday, 13 September 2023 03:13
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Data Subtraction question

Hi All

I am working on a V7R4 system (TR 7) and have been doing some work around
user profiles, specifically password expiry.

I want to provide details of the number of days elapsed since the user
password was changed so I ran the following query:

select *substring*(Authorization_name, *1*,*2*),
*current_timestamp* as "today",
password_change_date as "pwd chg",
*date*(*current_timestamp*) - *date*(password_change_date) as "days"
from qsys2.user_info u
Order by *date*(*current_date*) - *date*(password_change_date);

When I check the "days" value it appears to "go wrong" when the day in the
month is below 12. This is on a system with date format set to DDMMYY. The
output looks like this:
UP, Today, Pwd chg, Days
CL 2023-09-13 13:07:53.739700 2023-08-14 06:26:37.000000 30 RE 2023-09-13
13:07:53.739700 2023-08-14 08:18:42.000000 30 BR 2023-09-13 13:07:53.739700
2023-08-10 14:20:53.000000 103 DO 2023-09-13 13:07:53.739700 2023-08-10
08:21:32.000000 103 MC 2023-09-13 13:07:53.739700 2023-08-09 15:43:50.000000
104 RI 2023-09-13 13:07:53.739700 2023-08-09 09:39:54.000000 104 I think the
record for BR should be calculated as 34, not 103. This seems like an SQL
error to me, but there is every likelihood I should be doing something to
the dates before subtracting them.

Any suggestions as to how to fix this or get the correct result ?