This post is a continuation of a previous posting More SQL and dates part I, and is based on an email I received from Scott Damery.
"We can all become SQL black belts with knowledge and practice," he reassured me.
In this post I am going to compare SQL to RPGLE/RPG IV.
In all of these examples I am going to use a date data type field, containing the current date, as the "From date" and a literal, 06/03/1992, as "To date". The output from these SQL functions can be placed in either a numeric or an alphanumeric field.
Let's start with calculating the number of days between two dates:
exec sql set :Nbr = days(:FromDate) - days('1992-03-06') ;
Nbr = %diff(FromDate:d'1992-03-06':*d) ;
|
Why does he do that? Working for an international company there is often confusion on what the date is if it shown as 02/01/13. For Americans that is February 1, 2013. Europeans see it as January 2, 2013. And the Japanese can interpret it as January 13, 2002. By using the ISO format, YYYY-MM-DD, I have found that this avoids confusion.
I have used the %DIFF Built in Function to calculate the difference in RPG. Notice that the literal for the "To date" has a 'd' immediately before it. This denotes that the second parameter is a date. I have used the abbreviation '*d' in the third parameter rather than '*days', either is acceptable.
The result of both of these calculations is identical, 7866.
Calculating the difference in months looks similar, but the results are different:
exec sql set :Nbr = month(:FromDate) - month('1992-03-06') ;
Nbr = %diff(FromDate:d'1992-03-06':*m) ;
|
RPG accurately calculates that there are 258 months between today and March 6, 1992. Why does SQL return 6?
The MONTH function in SQL extracts the month from FromDate, 09, and from the literal, 03, before performing the calculation. Therefore, 09 - 03 = 6.
As expected the calculations for difference in the years returns the same value, 21:
exec sql set :Nbr = year(:FromDate) - year('1992-03-06')
Nbr = %diff(FromDate:d'1992-03-06':*y) ;
|
The final example Scott provided is something I don't think is easily available in RPG.
exec sql set :Alpha = date(:FromDate) - date('1992-03-06') ;
|
The field Alpha contains the value 210611, which means 21 years, 6 months, and 12 days. I can take that and format it into:
Text = 'The difference between the two dates is ' +
%subst(Alpha:1:2) + ' years, ' +
%subst(Alpha:3:2) + ' months, and ' +
%subst(Alpha:5:2) + ' days.' ;
|
'The difference between the two dates is 21 years, 06 months, and 12 days.'
You can learn more about all of these SQL functions on IBM's website:
This article was written for IBM i 7.1, and it should work with earlier releases too.
Keep up the great works.
ReplyDeleteSQL gives RPG programmers new sets of tools (op codes) to works with.
Thank you Sir.
Terrific tips. Thank you.
ReplyDeleteI've found the articles in RPGCOM.COM very interesting and i will follow this site.
ReplyDeleteTks Simon
It's really useful. Thanks.
ReplyDeleteGood article. I had to figure out how to calculate the number of months between dates with SQL after reading it. Here is one way that seems to work:
ReplyDeleteselect int((date('2013-09-18') - date('1992-03-06')) / 10000) * 12
+ int((((date('2013-09-18') - date('1992-03-06'))
- int((date('2013-09-18') - date('1992-03-06')) / 10000) * 10000)) / 100)
It strips out the number of years * 12, then strips out the number of months and adds it. The remaining days are ignored.
Whats wrong with using SQL’s MONTHS_BETWEEN() function?
ReplyDeleteExec sql values MONTHS_BETWEEN('2005-01-17', '2005-02-17') into :months;
That works too
Delete