Re: SQL Data Subtraction question

[Evan Harris <auctionitis@xxxxxxxxx>]

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 ?