CODEWITHSUNDEEP
sqldb2timestamp
aks
aks

Reputation: 1859

finding time difference in DB2

How can I write a query in DB2 for following thing:

The difference between current timestamp and a timestamp field in dB should be >=4 hours AND <= 24 hours

Someone suggested this but it's not working.

select * from tableName where 
                 date <=  DATEADD([hour], -4, CURRENT_TIME) and 
                 date date >=  DATEADD([hour], -24, CURRENT_TIME)

But it's not working. It's giving following error.

SQL0104N  An unexpected token "[hour]" was found following "ortdate <=  
DATEADD(".  Expected tokens may include:  "<func_arg_list>".  SQLSTATE=42601

Upvotes: 0

Views: 3572

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753575

DB2 doesn't like square brackets around name - that is a MS SQL Server mannerism.

The only reference to DATEADD() in the DB2 9.7 Info Centre (oh, beg its pardon: Center - one day, American's will learn to spell correctly) is in 'All of the following expressions are in the package com.ibm.alphablox.bloxbuilder.lib.expression', which is puzzling. I suspect the search is erroneous - though going to the SQL Manual and finding the functions listed there, DATEADD is conspicuously absent, so maybe it isn't.

So, you are going to have to manual bash for the DB2 syntax. But, if anything is going to work, it is likely to involve:

DATEADD(HOUR, -4, CURRENT_TIME)

rather than any square brackets. However, a somewhat more extensive search, including the RedBook on DB2 and Oracle Compatibility, does not show DATEADD as a function that is supported by DB2. So, the DATEADD route is doomed to ... have problems.

Since DB2 (still) doesn't have a proper (SQL standard) INTERVAL type, you are into investigating 'durations'. See DevX for an explanation - but beware the number of cookies the site '.qnsr.com' wants to set. And read the manuals at the DB2 Info Centre.

Upvotes: 0

Janick Bernet
Janick Bernet

Reputation: 21184

select * 
from   table t
where  t.tscolumn between current timestamp - 24 hours 
                      and current timestamp - 4 hours

Upvotes: 3

Salil
Salil

Reputation: 47472

Use just Hour instead of [hour]

select * from tableName where 
                 date <=  DATEADD(Hour, -4, CURRENT_TIME) and 
                 date date >=  DATEADD(Hour, -24, CURRENT_TIME)

Upvotes: 0

Related Questions