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:
1 | #### Docs modified during the LAST YEAR ; ex: >= 01/01/2016 00:00:00 et < 01/01/2017 00:00:00 |
5 | WHERE r_modify_date >= DATEFLOOR(year,DATEADD(year, -1, DATE(TODAY))) |
6 | 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 >= “
2 | 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 |
4 | 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)) “
2 | 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 |
4 | 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.
2 | select count(object_name) from dm_document where DATETOSTRING("r_creation_date",'mm/yyyy') = DATETOSTRING(DATE(TODAY),'mm/yyyy'); |
4 | select count(object_name) from dm_document where DATETOSTRING("r_creation_date",'mm/yyyy') = DATETOSTRING(DATEADD(year, -1, DATE(TODAY)),'mm/yyyy'); |
2 | SELECT * from dm_document WHERE DATEDIFF(day, r_modify_date,DATE(TODAY))<7; |
4 | 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 :
1 | 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; |
2 | dte_today = 14/02/2017 00:00:00 |
3 | dte_today = 14/02/2017 11:23:53 |
1 | SELECT count(r_object_id) |
4 | DATETOSTRING(a.r_modify_date,'yyyy') = (DATETOSTRING(DATE(TODAY),'yyyy')-1) |
That’s all!!!
Huseyin OZEVEREN
Related
