If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Add a day in SQL based query (Pass through)

Started by HvdO, 06 Jul 2022 01:35:40 AM

Previous topic - Next topic

HvdO

I try to make a query in Cognos using a SQL Pass through syntax with a date parameter.
This query has the following syntax:

Where d.EndDate  greater then or equal to to_date(#sq(prompt(FromDate, date))#,YYYY-MM-DD)

I use a prompt page to fill in the parameter using the Calender UI.

I would like to change the filter option that when a user select f.e. the first of january in the prompt page that the resultset contains all dates from the last of december and so on.
So I am looking for a way to add one day to the filter.

I tried to change the syntax into To_Date(dd_end_per_alg,DD/MM/YYYY) + 1 greater then or equal to to_date(#sq(prompt(FromDate, date))#,YYYY-MM-DD)

This works in Oracle SQL Developer, but I dont receive any result in Cognos.
When I dont use this as a filter, but use To_Date(dd_end_per_alg,DD/MM/YYYY) + 1 in my select query, it give a result of one century ago (31 dec 1921).

Can someone give me advise to solve this issue?