CODEWITHSUNDEEP
sqldb2db2-400
Mustapha George
Mustapha George

Reputation: 2527

Compare dates db2/400 vs SQL Server

I want to compare a date/time from SQL Server (type datetime-16) with db2/400 (type Z).

Typically I would like to know that that there are 60 seconds between 18:44 and 18:45

Each one has slightly different display characteristics.

SQL Server  2016-07-26 18:45:00.000
Db2/400     2016-07-26-18.45.00.000000

If I do this on my db2 database:

 SELECT 
     MYDATETIME, 
     '2016-07-26 18:44:00.000', 
     MYDATETIME -  '2016-07-26 18:44:00.000' 
 FROM @dates

I get this 

 MYDATETIME                  Constant value                  Numeric Expression     
 2016-07-26-18.45.00.000000  2016-07-26 18:44:00.000                 100.000000

Db2 SQL seems quite generous to accept slightly different formats.

But query tells me that 1 minute difference = 100. Looks like I have a base 10 comparison happening here.

Using db2 timestamp function, I get the same result

SELECT 
    MYDATETIME, 
    '2016-07-26 18:44:00.000', 
    MYDATETIME - timestamp('2016-07-26-18.44.00.000000') 
FROM @dates

How can I make comparisons that would give me difference in minutes (or hours or days)?

Upvotes: 1

Views: 579

Answers (2)

Charles
Charles

Reputation: 23793

The results of subtracting two timestamps on DB2 for IBM i is known as a duration

You might find TIMESTAMPDIFF() useful

select                                                           
   timestampdiff(2                                               
                , char(timestamp('2016-07-27 08:35:00.000000')   
                        - timestamp('2016-07-27 08:34:00.000000')
                      )                                          
                )                                                
from sysibm.sysdummy1

note that the first parameter is a small int with the following values

1   Microseconds
2   Seconds
4   Minutes
8   Hours
16  Days
32  Weeks
64  Months
128 Quarters
256 Years

The second parameter must be a character representation of a duration.

EDIT As pointed out by Clockwork-Muse, TIMESTAMPDIFF() is best used for short durations less than a month.

If you need accurate calculation for longer values, use the following form:

(DAYS(t1) - DAYS(t2)) * 86400 + 
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

Sample

select                                                           
    (DAYS(timestamp('2016-07-27 08:35:00.000000'))               
      - DAYS(timestamp('2015-07-27 08:35:00.000000'))            
    ) * 86400 +                                                  
    (MIDNIGHT_SECONDS(timestamp('2016-07-27 08:35:00.000000'))   
     - MIDNIGHT_SECONDS(timestamp('2015-07-27 08:35:00.000000')))
from sysibm.sysdummy1

Upvotes: 4

Brad
Brad

Reputation: 2320

I know this is DB2/400, but in my experience the time operators work the same way, so from this article:

SELECT 
    MYDATETIME, 
    '2016-07-26 18:44:00.000', 
    MYDATETIME - timestamp('2016-07-26-18.44.00.000000') SECONDS
FROM @dates

Since I don't have a DB2 instance to test this on, the next thing I would try would be this:

SELECT 
    MYDATETIME, 
    '2016-07-26 18:44:00.000', 
    SECOND(MYDATETIME - timestamp('2016-07-26-18.44.00.000000'))
FROM @dates

Upvotes: 0

Related Questions