How to select records on a date which is string stored in the database.e.g

Table 1

 

IRN       Note

68360  27/02/2006
68362  16/12/2005
68486  15/04/2005
68488  20/04/2005
68490  16/06/2005
68493  18/04/2005
68495  14/06/2005
68496  21/04/2005

 

Table 2

 

IRN        NOTE

68488  Open
68490  Until revised
68496  Open
68498  Open
68500  Open
68708  Open
68710  Open
68712  Open
68714  Open
68716  Open
68742  21/08/08
68749  Open
68751  Open
68767  Open
68769  Open
68971  Open
92025  Open
92061  03/12/2006
92367  Open
92373  14/05/2007
92715  Open
92728  Open
93090  Open
93100  Open
93121  Open
93802  Continuing

 

 

The NOTE field is nvarchar. Do I need to convert to date type?

This report is going to select records based on Table 1 and table 2

where table2's Note field has to be converted from OPEN to '31/12/2050' first before being selected by report, is it achievable?

 

In my record selection I have used:

if {NL6.NOTE} = 'OPEN' then
   {NL6.NOTE} = '31/12/2050' and
{NL5.NOTE} >= {?Start Expiray Date} and
{NL6.NOTE} <= {?End Expiray Date}

the {?Start Expiray Date} and {?End Expiray Date}  are string type parameters

But it didn't work, could somebody please advise? Thanks in advance.

 

John

 

Unfortunately, firstly  the Table 1 contains lots of other values except 'xx/xx/xxxx', secondly, as you indicated it will fail when it hits Until revised".Until revised", 'continuing', $1000, etc. I might not be able to create a formula to handle anything like these.

Hence I make this report select based on table2.NOTE = 'Open' and table1.NOTE <> NULL.

I was thinking to creating a view on SQL server to filter out values other than 'xx/xx/xxxx' in table 1( it will drop out many associated records in a sense), but table 2 wouldn't be possible.

 

I have advised to the customer that I am not going to select records based on these two tables, one of the reason that if I may be able to filter out some garbage, and the user will still keep keying garbage, and the reports will fail. As a result I make this report where Table1.NOTE <> "" and Table2.NOTE <> ""