What do you mean by "doesn't work"?  
What are some sample values in the J_DATE field?  
Just out of curiosity, what happens with the following query:
SELECT * FROM "AP_TRANSACTIONS"
WHERE J_DATE >= '2004-07-01' AND J_DATE <= '2008-07-18'
ORDER BY J_DATE ASC
 

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Thanks mirtheil for your reply!

"What do you mean by "doesn't work"?  "

It doesn't return any rows result.  And I know there's data since the year 1998 gregorian calendar in a gregoriana calendar field.

"What are some sample values in the J_DATE field? "

J_DATE
1999015
1999074
1999225

"Just out of curiosity, what happens with the following query:"

SELECT * FROM "AP_TRANSACTIONS"
WHERE J_DATE >= '2004-07-01' AND J_DATE <= '2008-07-18'
ORDER BY J_DATE ASC

I get the following error:

ODBC Error: SQLSTATE = 22005, Native error code = 0
Error in assignment.

But If I remove the hyphens in the dates the query runs but returns no results.

J_DATE >= '20040701' AND J_DATE <= '20080718'

And I know there's data between those dates in gregorian of course as I mentioned above.

btw it is a requirement to run the query on the j_date field

any other suggestion?

Thanks again!
 

just a bit more info:

the J_DATE (julian date)field is numeric type with prec 6 scale 0 and
 
APF_DATE (greg. date) is type char size 8

Maybe a some kind of conversion is needed here.

I think a conversion might be needed but it's not a database conversion.  
Try the following:
J_DATE >= '2004071' AND J_DATE <= '2008718'

I think there's an algorithm that strips the leading zeroes in some cases.  You might have to ask the vendor/developer of the application to find out how they store the dates.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Thanks mitheil!

It worked!