help with query please 1

[aldi]

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

 

[mirtheil]

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

 

[aldi]

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!

 

[aldi]

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.

 

[mirtheil]

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

 

[aldi]

Thanks mitheil!

It worked!