SQLRPGLE - Difference between two Dates

[izharz]

Hi Jamie,

Saw your code posted on net on "SQLRPGLE - Date manipulation within SQL ",
so thought to consult with you.
{
SELECT Item, Date(Days(SUBSTR(CHAR(MFDATE),1,4)||'-'||
SUBSTR(CHAR(MFDATE),5,2)|| '-'||
SUBSTR(CHAR(MFDATE),7,2)) + EXPIRE)
FROM ITEMMASTER
}

I am stuck with finding the no of days between two dates. Any idea how to
process with this.

Thanks in advance.

Regards

[jamief]

Re: SQLRPGLE - Difference between two Dates

Welcome to code400

here you go

  if you want do calculate the difference in either years or months or 
days,
use the OpCode SUBDUR or Built-in-Function %Diff(Date1: Date2: Time 
Code)

C     Date1         subdur    Date2         DiffDays:*D
C                   eval      DiffDays   = %Diff(Date1: Date2: *Days)

if you want to calculate the difference in years and months and days,
use SQL.
When substracting two date fields from each other with SQL, your result
numeric field with the following content.
Position 1-4 = years
Position 5-6 = months
Position 7-8 = days

D                 DS
D DiffDate                       8  0
D   DiffYears                    4  0 overlay(DiffDate)
D   DiffMonths                   2  0 overlay(DiffDate: *Next)
D   DiffDays                     2  0 overlay(DiffDate: *Next)

D Date1           S               D   inz(D'2004-07-01')
D Date2           S               D   inz(D'2003-01-31')

c/EXEC SQL
C+    set :DiffDate  = :Date1 - :Date2
c/END-EXEC

DiffDate = 10501 --> 1 year, 5 months and 1 day

[izharz]

Re: SQLRPGLE - Difference between two Dates

Thanks Jamie for your prompt reply. But sorry to bother you again.
I am getting the difference in years and months. My report wants it in days. By using the below mentioned code I am getting the difference in days its not correct as I am multiplying by 30 to convert month in days...

(substr(digits((DATE(substr(J$CLMS.J$MCK8,1,4) || '-' || substr(J$CLMS.J$MCK8,5,2) || '-' ||substr(J$CLMS.J$MCK8,7,2)))- 
(DATE(substr(J$CLLA.J$MFM8,1,4) || '-' || substr(J$CLLA.J$MFM8,5,2) || '-' ||substr(J$CLLA.J$MFM8,7,2)))),6,1)*[B]30[/B]) +
substr(digits((DATE(substr(J$CLMS.J$MCK8,1,4) || '-' || substr(J$CLMS.J$MCK8,5,2) || '-' ||substr(J$CLMS.J$MCK8,7,2)))- 
(DATE(substr(J$CLLA.J$MFM8,1,4) || '-' || substr(J$CLLA.J$MFM8,5,2) || '-' ||substr(J$CLLA.J$MFM8,7,2)))),7,8) as Days

Hope its clear...

Thanks

[jamief]

Re: SQLRPGLE - Difference between two Dates

Please what is the format of the dates in the table?


thanks

[izharz]

Re: SQLRPGLE - Difference between two Dates

Due to some reason its a character field and the dates are entered in the format YYYYMMDD

Thanks

[jamief]

Re: SQLRPGLE - Difference between two Dates

Its already an iso date you can just use the example I gave you and not do all that substr'ing and Cat'ing


let me know

jamie

[izharz]

Re: SQLRPGLE - Difference between two Dates

I am getting the difference by using it but in the format as you have mentioned. i.e 10501 --> 1 year, 5 months and 1 day.......
I want it in days.....

[B.Hauser]

Re: SQLRPGLE - Difference between two Dates

Hi,

assumed both dates are already dates or a valid character representation of a date('YYYY-MM-DD' or 'MM/DD/YYYY' or 'DD.MM.YYYY'), you can calculate the date difference in days as follows:

D Date1 S D inz(D'2006-02-08')
D Date2 S D inz(D'2005-12-31')
D DiffDays S 5I 0
*----------------------------------------------------------------
C/EXEC SQL Set Option DatFmt = *ISO
C/END-EXEC

c Clear DiffDays
C/EXEC SQL set :diffDays = Days( :date1) - Days( :date2)
c/END-EXEC

C DiffDays Dsply

By the way in RPG I'd prefere the RPG version to calculate the date difference in Days:
/Free
DiffDays = %Diff(Date1: Date2: *Days);
/End-Free

For more information about date caluclation look at the following Redbook Chapter 9.3.
Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Birgitta

[izharz]

Re: SQLRPGLE - Difference between two Dates

A big thanks to all. Its done by "Days( :date1) - Days( :date2)".

Regards,