Re: SQL Data Subtraction question

Hi Peter

Thanks for that, that certainly seems to provide the required result.

I feel like the documentation suggested subtracting one data from another
should work, so understanding how I misunderstood would be educational -
but this certainly solved the problem I asked about. Thanks again!


On Wed, Sep 13, 2023 at 1:58 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Evan,

Wrap each DATE() function in a DAYS() function, e.g.

values days( date('2023-09-13 13:07:53.739700') )
, days( date('2023-08-10 14:20:53.000000') )
, days( date('2023-09-13 13:07:53.739700') ) -
days( date('2023-08-10 14:20:53.000000') )

....+....1....
VALUES
738,776
738,742
34

From https://www.ibm.com/docs/en/i/7.5?topic=functions-days:

"The DAYS function returns an integer representation of a date.

The result is 1 more than the number of days from January 1, 0001 toD,
whereDis the date that would occur if the DATE function were applied to
the argument."


--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /

On 9/12/2023 6:13 PM, Evan Harris wrote:

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 ?