help with query please
help with query please
(OP)
Hello all,
I need a query for pervasive sql 2000i for a julian date field. the dates are between Jul 01, 2004 and Jul 18, 2008.
I tried this but doesn't work:
SELECT * FROM "AP_TRANSACTIONS"
WHERE J_DATE >= '2453187' AND J_DATE <= '2454665'
ORDER BY J_DATE ASC
Do I need to do a conversion? I don't know how
I'll appreciate your help
Thanks in advance,
aldi
I need a query for pervasive sql 2000i for a julian date field. the dates are between Jul 01, 2004 and Jul 18, 2008.
I tried this but doesn't work:
SELECT * FROM "AP_TRANSACTIONS"
WHERE J_DATE >= '2453187' AND J_DATE <= '2454665'
ORDER BY J_DATE ASC
Do I need to do a conversion? I don't know how
I'll appreciate your help
Thanks in advance,
aldi
RE: help with query please
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
RE: help with query please
"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!
RE: help with query please
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.
RE: help with query please
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
RE: help with query please
It worked!