INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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




 

RE: help with query please

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

RE: help with query please

(OP)
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!
 

RE: help with query please

(OP)
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.

RE: help with query please

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

RE: help with query please

(OP)
Thanks mitheil!

It worked!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close