[SOLVED] Select statement help needed

[deanes02]

select * from mytable WHERE JobRef BETWEEN '2007-05-01' AND '2007-05-06' ORDER BY JobRef

 

This is the statement I would like to use (or something similar).  It is trying to find Jobs between two dates.  The problem is the column JobRef is actually in the form AR2007-05-04 and EX2007-05-05 etc.  Can I get the select statement to ignore the first 2 characters of the column (preferred)?  Or do I have to create a new column with just the date and do the search on the date range on that (hopefully not)? Or some other solution?

 

Thanks for your help,

 

S

[Wildbug]

Untested, but try:

 

select * from mytable WHERE RIGHT(JobRef,10) BETWEEN '2007-05-01' AND '2007-05-06' ORDER BY JobRef

 

You may have to add something else to force the substring to be interpreted as a date, but try that first.

[deanes02]

Sorry about the delay in coming back to you.  i tried the code and it worked perfectly.

 

You guys are great!!!

 

thanks!