Date problem with SQL out of Report Studio

[Newbiee]

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!

[Newbiee]

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 ;)