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?