Text In To Datetime Question

Using SQL 2000


I have created a DTS Packages to import data from our back office database, which uses DBF files. This works great, however the way in which the dates are stored in the DBFs are as follows: -

4th May 2006 = 05/04/A6 (The A was a desperate bid by the developers to get by the Y2K issues)

Now the data is in SQL, I have created a view for each table, which re-orders the date data so it is displayed as dd/mm/yyyy using the following syntax: -

substring([eff],4,2)+'/'+left([eff],2)+'/'+'200'+right ([eff],1) AS [Eff Date]

This now looks good, however we can not use this data in a query using the between start date and end date as it sees it as text.

How can I convert this data to Datetime?

I believe it can be done with a cast or convert command, however do not know the correct syntax nor know how to apply it to our date columns in each table/view.

Any suggestion will be gratefully received.