CODEWITHSUNDEEP
db2
Koushik Chandra
Koushik Chandra

Reputation: 1491

DB2 - difference between two timestamp in days

how to get exact difference between two timestamp in days in DB2. I mean if one date is FROM_DATE=5/6/2015 2:22:27.000000 PM and TO_DATE=3/30/2015 2:33:52.000000 PM, then the timestamp difference should show 36 days. I tried using below

((24*DAYS(From_Date)+MIDNIGHT_SECONDS(From_Date)/3600) -

        (24*DAYS(To_Date)+MIDNIGHT_SECONDS(To_Date)/3600))/(24)

But this is giving me the difference as 37 days.

Upvotes: 0

Views: 244

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12454

Wrong formula. Check out the following.

SELECT
  FROM_DATE, TO_DATE
,
(
  (24*DAYS(From_Date)+MIDNIGHT_SECONDS(From_Date)/3600) 
- (24*DAYS(To_Date)+MIDNIGHT_SECONDS(To_Date)/3600)
)/24 AS D1
, 
(
  (DAYS(From_Date)*bigint(86400) + MIDNIGHT_SECONDS(From_Date))
- (DAYS(To_Date)  *bigint(86400) + MIDNIGHT_SECONDS(To_Date))
)/86400 AS D2
FROM 
(
VALUES 
  (TIMESTAMP('2015-05-06-14.22.27'), TIMESTAMP('2015-03-30-14.33.52'))
, (TIMESTAMP('2015-03-31-14.22.27'), TIMESTAMP('2015-03-30-14.33.52'))
, (TIMESTAMP('2015-04-01-14.22.27'), TIMESTAMP('2015-03-30-14.33.52'))
) T(FROM_DATE, TO_DATE);

|FROM_DATE                 |TO_DATE                   |D1         |D2                  |
|--------------------------|--------------------------|-----------|--------------------|
|2015-05-06-14.22.27.000000|2015-03-30-14.33.52.000000|37         |36                  |
|2015-03-31-14.22.27.000000|2015-03-30-14.33.52.000000|1          |0                   |
|2015-04-01-14.22.27.000000|2015-03-30-14.33.52.000000|2          |1                   |

Upvotes: 0

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

What about

SELECT days_between ('2015-05-06-02.22.27.000000', '2015-03-30-02.33.52.000000')
FROM SYSIBM.sysdummy1

It returns 36.

Upvotes: 2

Related Questions