RE: sql date difference gotcha

I might see if using the days function on both is more certain to be correct than TIMESTAMPDIFF is - the latter makes assumptions, as expressed here with an example that gives an odd result - I list 2 of them that are definitely always true.



* One year has 365 days.
* One month has 30 days.
*

Here's an interesting example -
The use of these assumptions imply that some result values are an estimate of the interval. Consider the following examples:
* Difference of 1 month where the month has less than 30 days.

TIMESTAMPDIFF(16, CHAR(TIMESTAMP('1997-03-01-00.00.00') - TIMESTAMP('1997-02-01-00.00.00') ) )The result of the timestamp arithmetic is a duration of 00000100000000.000000, or 1 month. When the TIMESTAMPDIFF function is invoked with 16 for the interval argument (days), the assumption of 30 days in a month is applied and the result is 30.
So timestamp arithmetic gives us the timestamp duration to play with. I guess we must always be kind of careful - maybe the only thing we can "guarantee" is something in number of units less than or equal to days - those assumptions seem to be consistent over long periods of time.


Oh where is Bruce Vining when we need him for his "time"ly remarks in this area?


Regards
Vern


On Mon, 25 Mar, 2024 at 5:17 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:


To: 'midrange systems technical discussion'

If you want to calculate the difference in days, you have to convert both
dates into the (calculated) number of days since '0001-01-01' and the
substract both numbers from each other:

Values(Days(Current_Date) - Days(Date('02/22/2024'));

Another option would be to use the TIMESTAMP_DIFF Function.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
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<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of
smith5646midrange@xxxxxxxxx<mailto:smith5646midrange@xxxxxxxxx>
Sent: Monday, 25 March 2024 15:10
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: RE: sql date difference gotcha

I just played with this a little and it looks like the value returned is in
YYYYMMDD digits. Therefore 102 is 1 month and 2 days. I did current date -
'03/24/2024' just to see and it returned 10001.



-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of eric
bothes
Sent: Monday, March 25, 2024 8:53 AM
To: Midrange-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: sql date difference gotcha

just an fyi for anyone using the following logic and thinking it will return
the number of days difference as i though.

i was expecting the following results

1, 2, 3, but i was incorrect.

assume current date = 03/25/2024

SELECT
current date - '03/24/2024' as days1,
current date - '02/23/2024' as days2,
current date - '02/22/2024' as days3 FROM SYSIBM.SYSDUMMY1

the results are

1, 102, 103, where results returned are yymmdd format.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.