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

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Error implementing Datetime filter

Started by lifzgud, 23 May 2010 12:40:39 AM

Previous topic - Next topic

lifzgud

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

SynexusStuart

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