Re: SQL Data Subtraction question

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 ?