Date Arithmetic
Moderators: chulett, rschirm, roy
Date Arithmetic
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
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
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.+
Supporting functions:
Function KBAConvDateToYYYYMMDD
Function KBADateIconv
Function KBASubtractDaysFromDate
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
Function KBAConvDateToYYYYMMDD
Code: Select all
DEFFUN KBADateIconv(A) Calling "DSU.KBADateIconv"
Ans = OCONV(KBADateIconv(Arg1), "D-YMD[4,2,2]")
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
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]")
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Kumar,
I have a quickie function appended to this post that you can use:
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.
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")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Date arithmetic
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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....
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....
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom