Query 400- Number of Days Calculation

185 pts.
Tags:
query 400
Hi,

Using Query 400, i want to find difference between julian dates. It should include both leap year and non-leap year.

Suggest any idea.

 

 



Software/Hardware used:
as/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Vjaya,

I havent tried the similar case in QUERY/400.
But, I found a solution by searching it in google.

http://www.itjungle.com/fhg/fhg062806-story01.html

You can find the way to do the same(with sample code) in above link.
Hope, it will help you.

Pradeep.

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Any options other than query/400? Phil
    50,595 pointsBadges:
    report
  • VJayashree
    No Phil, I have to do it using query/400.
    185 pointsBadges:
    report
  • philpl1jb
    You're in for a great deal of pain ..as shown in the link Pradeep offerfed. Query/400 is mature and doesn't offer SQL date operations. Phil
    50,595 pointsBadges:
    report
  • TomLiotta
    I have to do it using query/400. Why? Nobody else has to do it using Query/400. First thing you need to understand is that "Julian" is a date format. It is not a data type. DATE is a data type. And you can format a date any way you need for presentation. Also, a 5-digit numeric field can hold a value in Julian date format. Do you have a DATE field or is it a numeric field with a Julian date value? Tom
    125,585 pointsBadges:
    report
  • VJayashree
    Hi Tom, ya. It is a numeric field with a Julian date value. for EX: 111350 like this. I have to find the difference by considering it as a date field and not a numeric field. Please suggest any idea, Thanks. Jayashree V.
    185 pointsBadges:
    report
  • TomLiotta
    It is a numeric field with a Julian date value. for EX: 111350... For Query to recognize that as a date value while keeping the Julian format, I think that you need to create a 7-digit result field and assign it a value of 1900000 + (111350). That is, add 1900000 to your numeric field so that your 7-digit result field will contain 2011350. You should then be able to turn that into a 7-character result that can actually become a real DATE result. You can possibly get away without using an intermediate numeric result field, but it might help reduce complexity in the conversion functions. Once you have the values converted into real DATE fields, date durations will be much easier. Doing it outside of Query/400 is still a better option. And not using numeric fields with odd values like you're stuck with would be even better, but that's probably not a choice that you have at the moment. I'd still like to know why you have to use Query/400. Every AS/400 since maybe version 2 of OS/400 has included alternatives as part of the base system. Are you still using version 1? Or is this a homework assignment? (Or is it just that the alternatives aren't known?) Tom
    125,585 pointsBadges:
    report
  • VJayashree
    Hi Tom, Thanks for ur explanation. The requirement is to be done using query 400. When we convert date fiels to a 7 digit character, how it will be possible to convert it to a normal date format(MM/DD/YYYY)? Jayashree
    185 pointsBadges:
    report
  • TomLiotta
    The requirement is to be done using query 400. We understand that. We just want to know why anyone would make such a poor requirement when other alternatives are available and better. We want to know in order to understand the problems that people face. When we convert date fiels to a 7 digit character, how it will be possible to convert it to a normal date format(MM/DD/YYYY)? When it's converted to "7 digit character", what format are the digits in? In any case, in order to display a "7 digit character" value as "date format(MM/DD/YYYY)", the first thing to do is convert it back to a real DATE field. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    The easist way to do what you want in Query/400 - build a file with three columns - Julian date - YYYYMMDD date - Number of days after some starting point (Excel users 1900) - Populate this file with the correct values throughtout the range you will need - Query/400 joins the file as necessary to go from YYYYMMDD to Julian and adds or subtracts one NumberOfDay from another. Phil
    50,595 pointsBadges:
    report
  • TomLiotta
    The easist way to do what you want in Query/400 - build a file with three columns If the function is going to be needed more than once or twice, that's probably true. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    The first time is the hardest.
    50,595 pointsBadges:
    report
  • VJayashree
    Hi all, Thanks for your update I found a way to find date difference calcuation The below expression converts the jde julian date to normal date. DATE(SUBSTR((DIGITS(T01.SDIVD+1900000)),2,7)) Then the difference can be calculated as DAYS(ARRDT)-DAYS(SODATE) These statements solved my problem and now the days are calculating perfectly.
    185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following