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

Author Topic: Add a day in SQL based query (Pass through)  (Read 157 times)

Offline HvdO

  • Associate
  • **
  • Join Date: Jul 2022
  • Posts: 1
  • Forum Citizenship: +0/-0
Add a day in SQL based query (Pass through)
« on: 06 Jul 2022 01:35:40 am »
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?

« Last Edit: 06 Jul 2022 01:43:09 am by HvdO »