datebetweencognos

Cognos Where Clause on Date column?

I am a SQL DBA trying to get some information in Cognos. The column name is [Packingdate] column.

In order to retrieve last 7 days of packing data information I use the below.

where [Packingdate] >_add_days( current_date, -7 ) -This is working fine.

However when i tried to get November month data I tried the below

[Packingdate] between '2021-11-01' and '2021-11-30'

It is giving me the below error

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.

Ami missing something?

Solution

  • Remove the quotes:

    [Packingdate] between 2021-11-01 and 2021-11-30

    When dealing with date or datetime data, YYYY-MM-DD (without quotes) is the format Cognos understands.