Hi Experts,
I am facing strange issue with Manual sql in Report studio. I have to used a already created query which calculated the difference between the two dates(Complex calculation). Its running fine at database and giving correct output. But when I put same query in manual sql at report studio it gives different output. database(Oracle) have dates with the timezones and it calculates the difference considering the timezone. But same query run from report studio is giving different output as its not considering the time zones . Normally it should return the same result as per the query. I tried changing the properties of the SQL to pass-through But no luck.
Anybody has faced the same issue ?
Theorizing here, so more of an idea than an answer... When you run it on the database server, everything is localized to the database server. When you are using a client-side tool (e.g. Report Studio), it may be passing the local client-side timezone to the database, and Oracle takes that into account. (This could be the timezone of the Cognos server.) Oracle may provide a way to override such behavior, you'd have to look at the Oracle client documentation.
It's just a theory, but if you have set the query as pass-through it should be sending the SQL as-is to the database, hence my thought that it is something related to client configuration.