IBM Cognos _days_between function not working

Question

I'm having an issue with Cognos 10. I'm trying to calculate the number of days between to dates so I use the _days_between( date1, date2 ) function.

_days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme])

I'm quite sure the two Dates are Date objects ( I set them to Dates in the properties anbd the data type from the DB is Date), but I get this error :

QE-DEF-0459 CCLException
QE-DEF-0260 Erreur d'analyse syntaxique avant ou près de la position 55 de '_days_between([Derniere Date Changement diaphragme],'
QE-DEF-0261 QFWP - Analyse du texte : days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme])

Sorry for the error being in french, the error is a parsing error. I tried to use the function with "hard" dates, other dates from my DB, but nothing works!

EDIT : The function works only if I use _days_between( date1 ; date2) instead of _days_between( date1, date2) (Why?)

But I still can't calculate the number of days between the two dates. The function returns 0. However, _days_between( [Derniere Date Changement Diaphragme] ; 2015-05-01) works fine.

Any idea what's happening over here?

Answer 1

The Cognos _days_between function works with dates, not with datetimes.

Some databases, like Oracle, store all dates with a timestamp.

1. On a query directly to the datasource, try using the database's functions to get this data instead. When possible, this is preferable as it pushes work to the database which is faster.

Try this in oracle:

abs([Derniere Date Changement diaphragme]-[Premiere Date Changement diaphragme])

Edit: The error happening on only one of your fields suggests an issue with that date field. Run a query directly against your MySql datasource and see if the raw data is coming through as a date correctly.

To do a date diff in MySql: DATEDIFF( [Derniere Date Changement diaphragme], [Premiere Date Changement diaphragme] )

Edit: This second point is not relevant in Cognos 10. It can be disregarded

2. When you must use cognos functions, convert your incoming date-with-time (datetime) fields to Date without any time (date) with a

   CAST([Derniere Date Changement diaphragme],date)