Hi all,
as you see I have a problem with the SQL functions in Cognos 8.3.
The User wants to select two date-time value and my SQL should find the values between.
The SQL looks like this
Select a,b,c,d
from table_a
where Location = #prompt ('param_Location')#
and country = #prompt ('param_Country')#
and
Datetime
between
to_date(#prompt('param_Starttime')#,'DD-MON-YYYY HH:MI:SS')
and
to_date(#prompt('param_Endtime')#,'DD-MON-YYYY HH:MI:SS')
When I execute I get following error:
'sqlOpenResult' Status='-28'.
I found out that the problem is the date-format. The Column "Datetime" has the Oracle format "date" - but it is date and time (for example 12.NOV.2008 16:30:14)
I tried several things like
to_date(#prompt('param_Endtime','date')#,'DD-MON-YYYY HH:MI:SS')
or
to_date(#prompt('param_Endtime','date_time')#,'DD-MON-YYYY HH:MI:SS')
or
to_date(#prompt('param_Endtime','datetime')#,'DD-MON-YYYY HH:MI:SS')
but nothing worked.
The only statement that worked was
Datetime between TO_DATE(#sq(prompt('param_Starttime', 'Date'))#, 'YYYY-MM-DD') ...
But with this statement I dont have the hours, minutes and second ...
I looked through Cognos KB but did not find a solution ...
Can someone please tell me which statement could work?
Every Idea helps!
Hi all!
It was pretty hard and took me almost 1 day with trying different statements but now I found the solution:
The problem is, that Oracle Database has the format "Date" which includes also Time without milliseconds - and the Cognos type "datetime" includes the milliseconds.
To make it work use the Date- and Time prompt and write following statement in your sql:
where
...
and
t.starttime >= TO_DATE(substr(#sq(prompt('param_Startdatetime', 'Datetime'))#,1,19), 'YYYY-MM-DD HH24:MI:SS')
and t.endtime < TO_DATE(substr(#sq(prompt('param_Enddatetime', 'Datetime'))#,1,19), 'YYYY-MM-DD HH24:MI:SS')
I hope that someone else can use this ;)