For update purposes I have had to store a date in a text field format. I want a query which searches all the dates and brings up only one which later than todays date. this is how far i have got but i get the wrong answer i think it is because it is stored as text.

Select top 1 * from nextmatch where date > now();

date matchdesc
23/11/2003 Factory Bank, Ramsey

This is what i get, and as you can see this is not right.

date matchdesc
05/10/2003 Farm Shop Lake
02/11/2003 Old Nene March
23/11/2003 Factory Bank, Ramsey
14/12/2003 Old Nene Benwick
04/01/2004 Ramsey, St Mary's
01/02/2004 Horseway Sluice, Chatteris
29/02/2004 Ramsey, St Mary's

Thanks for the help

in your where clause


where cast( substring([date], 4,2)
+ '/' + substring([date], 1,2)
+ '/' + substring([date], 7,4)as datetime) > getdate()


just looks like you need to put it back in the proper format and convert it back to date time.

if that does not work let me know.