Extract date from string 1

[swiss2007]

Hi,
I have a date field "Edition_date" which has to be filtered based off on 2 dates that have to be extracted from a string field "Title".
The start date in the Title always comes after "startday" and End date always comes after "Endday"

My Title sample data is like

1)Cause: Processing Partner,Failure Category edition Date: Startday 11/15/2008 06:00 AM; edition date: Endday 01/12/2008 10:30

PM;
2)Perforamnce Partner,not categerized, Random result edition Date: Startday 10/10/2008 06:00 AM; edition date: Endday

11/20/2009 07:30 PM;

the startday and the endday formats are always in mm/dd/yyyy format


I should be able to say something in the record selection like
Edition_date in Title[startdate] to Title[enddate]


Thanks in Advance

 

[lbass]

Create a record selection formula like this:

stringvar x := {table.title};
datevar start := cdate(extractstring(x, "Startday ", " "));
datevar end := cdate(extractstring(x, "Endday ", " "));
{table.date} in start to end

This won't be fast however.

-LB

 

[swiss2007]

Any idea how worse the performance could be.

 

[lbass]

Only you can tell us that. The formula will not pass to the SQL query. You might want to try creating two SQL expressions instead, like this:

[{%start}:]
{fn substring(table.`title`,{fn locate('Startday',table.`title`)}+10,10)}

[{%end}:]
{fn substring(table.`title`,{fn locate('Endday',table.`title`)}+10,10)}

Then use a record selection formula like this:

{table.editiondate} in {%start} to {%end}

This would result in a faster report. The functions available to you and the punctuation are dependent upon your datasource, so you would have to adjust the above accordingly.

-LB

 

[swiss2007]

Thanks again for the additional information but the problem is that the title is not stored in the database but it has to be parsed from the App.

 

[lbass]

Not sure what you mean by that, except that you couldn't use a SQL expression then. Did the first method work?

-LB

 

[swiss2007]

Hi lbass,
I tested the first formula and it worked absolutely well.I mean that the Title is going to be passed to the report from the application at the report run time and only then I capture the startday and endday dates.