Query 400- Number of Days Calculation
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
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
ASKED:
December 27, 2011 7:24 AM
UPDATED:
February 28, 2012 1:38 PM
Approved Answer - Chosen by VJayashree (Question Asker)
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.
Other Answers:
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.
To see other answers submitted to the Answer Wiki: View Answer History.
Any options other than query/400?
Phil
No Phil,
I have to do it using query/400.
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
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
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.
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
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
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
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
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
The first time is the hardest.