Hi,
I am trying tol filter my report based on time.
That is i wont pick up any records that have a time stamp greater than 6:30.
I did the following steps.
Created a new end time
substring(cast(current_date,varchar(20)),1,20)+' '+'18:30:00.000'
put a filter
[Issue_Date]>=cast_timestamp([end time])
When i run the report
I get the following error
An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (SQLSTATE=22007, SQLERRORCODE=242)
However i use the generated SQL from Cognos and paste it in SQL Server it works fine.
I have tried a lot of options like converting it character and then to date but nothing works
try adding a new Data Item to the Query '[DataItem1]' using teh following syntax:
case when datepart({hh},[Issue_Date]) >= 7 then 1 when datepart({hh},[Issue_Date]) = 6 and (datepart({mi},[Issue_Date]) >= 30)
then 1 else 0 end
this will put a '1' in any record stamped after 6:30 and a '0' in any before.
Then drag [DataItem1] into Detail Filters and: [DataItem1] = 1
Should work.
Stuart