Track Back Error with Date Prompt

[cognos74]

Hi,

I have one Data Item([Value]) in my query.  It has alpha, Numeric and date values included in that column.
example data:
302__Refuses tests, treatments
203__Delay due to weekend
5
7
11/14/2016 10:10
11/15/2016 16:07

So my requirement is to pull the data using date from the [Value] Data Item.
I have created one calculated column([Data Item1]) like following:

cast(substring([Business View].[Nursing Care].[Value],1,10),date)

and then used this created column in the filter as following:
[Value] between ?startdate? and ?enddate?

but I am getting Track Back error.  any Idea why I am getting this error and how to make that calculated column in the filter to use my report.

[Lynn]

You cannot cast a value of 5 to a date. Nor can you cast a value of 302__Refus (first 10 characters of your first row) to a date.

It is not a good idea to mix content like this.

You could include a filter that only returns rows that look like a date with some pattern matching perhaps, but if there is any hope in getting the underlying data source to store dates as dates and numbers as numbers and text as text I'd say pursue that with vigor.

[cognos74]

Hi Lynn,

thanks you fro your reply,  can you please let me know that how to write a filter that only row that look like a  date because for this query I need to get only the row look like date.

Thanks,

[hespora]

if those are typical examples of your values, then

Code Select Expand

substr([value],3,1)='/' should do the trick

/edit: gah sorry, the Oracle bias.... it should be substring, not substr ;)

[cognos74]

Hi Hespora,  your idea does not work in this case. I already have the substring in my expression.
any other ideas to make it work??

[hespora]

Why would that be an issue?

One, as per your previous post, the substring is in your [Data Item1], not in [Value]
Secondly, Substring(Substring(...)) is not a problem
Thirdly, as Lynn already said, it's the casting that's throwing the error, not the substring.