to calculate number of days between two dates in dql documentum

Question

I have two attributes, effect_date,next_effect_date. I have to calculate the difference between these two dates in days. what will be the dql query? Please Help

Answer 1

Use this query for dm_document and date properties r_creation_date and r_modify_date which are generic object type & attributes:

select DATEDIFF(day, r_creation_date, r_modify_date)
from dm_document where r_object_id = '<set_id_here>'

When you specify object type name we can adjust this query. Syntax for DATEDIFF function is DATEDIFF(date_part, date1, date2)

Result value depends on DB under your repository. From documentation:

If the repository is using Oracle or DB2, the return value is a floating point number.
If the repository is using DB2, the server assumes 365 days per year and 30.42 days per month (the
mean number of days in a month). These assumptions can cause return values that differ from the
expected value. To illustrate, the following example, which asks for the number of days between
March 1, 1996 and Feb 1, 1996, returns 30.42 instead of 28:
DATEDIFF(day, date('03/01/1996 0:0:0'),
date('02/01/1996 0:0:0'))
If the repository is using MS SQL Server, the return value is an integer for all units except day. If you
specify day in the function, the return value is a floating point.
The MS SQL Server implementation round up if the difference is one half or greater of the specified
unit. The implementations round down if the difference is less than one half of the specified unit. To
illustrate, the following example, which asks for the difference between March 1, 1996 and July 1,
1996 expressed as years, returns 0 because the difference is only 4 months.
DATEDIFF(year,date('03/01/1996'),date('07/01/1996'))