help with query please 1

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

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

Thanks mitheil!

It worked!