Documentum : BUG in DQL Date Functions When Together Use Of DATEFLOOR, DATEDIFF, DATETOSTRING and DATEADD

Hello,

Just a post concerning the BUG of use of DATEFLOOR, DATEDIFF, DATETOSTRING and DATEADD together.

To illustrate this problem, for example, we would like to list the documents (r_object_id) modified during the last year:

#### Docs modified during the LAST YEAR ; ex: >= 01/01/2016 00:00:00 et < 01/01/2017 00:00:00
# error
SELECT r_object_id 
FROM dm_document 
WHERE r_modify_date >= DATEFLOOR(year,DATEADD(year, -1, DATE(TODAY))) 
AND r_modify_date < DATEFLOOR(year,DATE(TODAY));

==> Error occured during query execution :[DM_QUERY_E_SYNTAX]error: “A Parser Error (syntax error) has occurred in the vicinity of: SELECT r_object_id FROM dm_document WHERE r_modify_date >= “
 

# same error
SELECT r_object_id, r_modify_date, DATEFLOOR(year,DATE(TODAY)) as dte0, DATEFLOOR(year,DATE(NOW)) as dte1, DATEADD(year, -1, DATE(TODAY)) as dte2, DATEFLOOR(year,DATEADD(year, -1, DATE(TODAY))) as dte3
FROM dm_document 
WHERE r_modify_date < DATEFLOOR(year,DATE(TODAY))
;

==> Error occured during query execution :[DM_QUERY_E_SYNTAX]error: “A Parser Error (syntax error) has occurred in the vicinity of: SELECT r_object_id, r_modify_date, DATEFLOOR(year,DATE(TODAY)) “
 

# NO any error
SELECT r_object_id, r_modify_date, DATEFLOOR(year,DATE(TODAY)) as dte0, DATEFLOOR(year,DATE(NOW)) as dte1, DATEADD(year, -1, DATE(TODAY)) as dte2
FROM dm_document 
WHERE r_modify_date < DATEFLOOR(year,DATE(TODAY))
;

==> NO any error
 

==> The problem that’s because DQL does not allow to imbricate these functions DATEFLOOR, DATEADD, DATEDIFF and DATETOSTRING.

# OK
select count(object_name) from dm_document where DATETOSTRING("r_creation_date",'mm/yyyy') = DATETOSTRING(DATE(TODAY),'mm/yyyy');
# NOK : ERROR
select count(object_name) from dm_document where DATETOSTRING("r_creation_date",'mm/yyyy') = DATETOSTRING(DATEADD(year, -1, DATE(TODAY)),'mm/yyyy');

# OK
SELECT * from dm_document WHERE DATEDIFF(day, r_modify_date,DATE(TODAY))<7;
# NOK : ERROR
SELECT * from dm_document WHERE DATEDIFF(day, r_modify_date,DATEADD(month, -1, DATE(TODAY)))<30;

The alternative DQL query to return all documents modified in last year :

SELECT DATE(TODAY) as dte_today, DATE(NOW) as dte_now, DATETOSTRING(DATE(TODAY),'yyyy') as current_year, DATETOSTRING(DATE(TODAY),'yyyy')-1  as last_year from dm_docbase_config;
dte_today = 14/02/2017 00:00:00
dte_today = 14/02/2017 11:23:53
current_year = 2017
last_year = 2016

SELECT count(r_object_id) 
FROM dm_document a
WHERE 
DATETOSTRING(a.r_modify_date,'yyyy') = (DATETOSTRING(DATE(TODAY),'yyyy')-1)
;

That’s all!!!

Huseyin OZEVEREN