Date Arithmetic

[vskr72]

I have a Date field coming from a DB2 stage. I need to add 3 months to it and store that value seperately for further processing.

Just adding 90 days to that will not work right. Because, we need to take into consideration months with 28 days, 30 and 31.

I can convert the date to an internal format with IConv. Not sure how to proceed further. Any help is appreciated.

Kumar

[kcbland]

This DS Function, KBAAddMonthsToDate, takes Arg1 as the date and Arg2 as the integer number of months. It assumes you want to add whole months, and will give you N months into the future. If you add 3 months to 1/1, you should get an answer of 4/1. But, if you have 1/31 and add 3 months, you should get 4/31, which doesn't exist. The correct answer will be 4/30. The logic will round back to the maximum day in the month prior. These functions solve issues with DS going back to 1.0 and may be redundant for some fixes now available in DS 7.+

Code: Select all

      DEFFUN KBAConvDateToYYYYMMDD(A) Calling "DSU.KBAConvDateToYYYYMMDD"
      DEFFUN KBASubtractDaysFromDate(A,B) Calling "DSU.KBASubtractDaysFromDate"

      DrivingDate = KBAConvDateToYYYYMMDD(Arg1)
      DrivingYear = FIELD(DrivingDate, "-", 1)+0
      DrivingMonth = FIELD(DrivingDate, "-", 2)+0
      DrivingDay = FIELD(DrivingDate, "-", 3)+0

      AddMonths = Arg2

      If AddMonths >= 12 Then
         AddYears = INT(AddMonths / 12)
         AddMonths = MOD(AddMonths, 12)
      End Else
         AddYears=0
      End

      If AddMonths + DrivingMonth > 12 Then
         AddYears += 1
         AddMonths = AddMonths - 12
      End

      FirstTestDate = FMT(DrivingYear + AddYears, 'R%4'):"-":FMT(DrivingMonth + AddMonths, 'R%2'):"-":FMT(DrivingDay, 'R%2')
      TestDateAgain = KBAConvDateToYYYYMMDD(FirstTestDate)
*
*  Verify that internalization and then externalization gives you the same date back.
*  If not, then you went past the end of the month and DS BASIC treats February 31 as a valid date.
*  So, backup the date until the end of the previous month.
*
      If FirstTestDate # TestDateAgain Then Ans = KBASubtractDaysFromDate(TestDateAgain[1,8]:"01",1) Else Ans = FirstTestDate

Supporting functions:
Function KBAConvDateToYYYYMMDD

Code: Select all

      DEFFUN KBADateIconv(A) Calling "DSU.KBADateIconv"

      Ans = OCONV(KBADateIconv(Arg1), "D-YMD[4,2,2]")

Function KBADateIconv

Code: Select all

      Date = Arg1
      IntDate = ICONV(Date,"D")
      If IntDate = "" Then
         CONVERT "/ \:.,-_" TO "--------" IN Date
         Begin Case
            Case Date MATCHES '0n"-"0n"-"0n'
               yy = Field(Date,"-",1)
               mm = Field(Date,"-",2)
               dd = Field(Date,"-",3)
               IntDate = ICONV(mm:"-":dd:"-":yy,"D")
            Case LEFT(Date,10) MATCHES '4n-2n-2n'
               Date = LEFT(Date,10)
               yy = Field(Date,"-",1)
               mm = Field(Date,"-",2)
               dd = Field(Date,"-",3)
               IntDate = ICONV(mm:"-":dd:"-":yy,"D")
            CASE @TRUE
               IntDate = ""
         End Case
      End
      Ans = IntDate

Function KBASubtractDaysFromDate

Code: Select all

      DEFFUN KBADateIconv(A) Calling "DSU.KBADateIconv"

      BegDate = KBADateIconv(Arg1)
      SubDays = Arg2

      If BegDate # "" AND SubDays # "" THEN
         Ans = BegDate - SubDays
      End Else
         Ans = BegDate
      End

      Ans = OCONV(Ans, "D-YMD[4,2,2]")

[aesguerra]

hi,

why not do the date arithmetic within DB2?

[ArndW]

Kumar,

I have a quickie function appended to this post that you can use:

Code: Select all

Temp        = ICONV(Arg1,"D4/YMD")
CurrentYear = OCONV(Temp,"DY4")
CurrentDay  = OCONV(Temp,"DD")
Plus3Month  = OCONV(Temp,"DM")+3
IF (Plus3Month>12)
THEN
   CurrentYear += 1
   Plus3Month  -= 12
END ;** of if-then next year
Temp = ICONV(CurrentYear:'-':Plus3Month:'-':CurrentDay,"D4-YMD")
Ans = OCONV(Temp,"D4-YMD")

The 2nd to last line will do things like correctly handle dates such as 2004-11-29 (add 3 months giving 2-29 which is converted to 3-1). The code is split into separate lines so you can understand the logic a bit better, but it is intrinsically a simple operation.

[Sainath.Srinivasan]

A small change to make the better to best.

Use the last line as

Ans = OCONV(Temp,"D4/YMD") instead of

Ans = OCONV(Temp,"D4-YMD")

This will make the output format identical to input format.

[vskr72]

Hello Arnd,

Thank you for that. Further to that is is possible to make that 3 a variable.

In the sense, the value 3 is being pulled from another another DB2 table. Today it is 3. But, in our old project we used 2.

So, it might keep changing every time. Thank you again.

Kumar

[Sainath.Srinivasan]

The month difference (3 in this case) can be a variable in ArndW's script (as long as it is within 12 months or -ve)

[kcbland]

Not to be petulant, but with all respect, the function posted by Arnd has some flaws:

1. It does now allow you to add more than 12 months to a given date. If you ever need to add 14 months, you need the extra logic as in the function I posted.

2. It also does not allow for the fact that DS BASIC itself with treat February 30th and 31st as a valid date. If you are in November, and want 3 months added, your answer should probably be in February, correct? The function posted CAN give you MARCH, which is probably the WRONG ANSWER.

3. The extra supporting functions I posted will recognize almost every type of date and convert it to ISO standard YYYY-MM-DD, so you get that done automatically for you. You will have to conform all dates to the INTERNALLY FIXED format.


I'm not upset or anything, but when folks post solutions they need to make sure that they don't present more problems than they fix. In this case, people need to understand that date math has some internal flaws in the DS BASIC language that have to be accommodated. Ignore at your own risk....

[vskr72]

Thank you everyone for all the info. Appreciate your help.

Kumar.

[Sainath.Srinivasan]

Kenneth,

If the dates are different because of Nov 31st to Feb 31st, you can patch it with transforms like MONTH.LAST.

Catering for MonthDifference > 12 in this case will be a luxury than a need. In that case, the user can also expect -ve differences.