Hi,
Ive been using Datastage for a year now on two different
projects, and Ive been avoiding the question of adding
months to a date, since I have not found the possibility
within Datastage.
Ive now met the client who is very keen on having the
possibility to add months to a date. (Like the command
ADD_MONTHS within the SQL language)
Im interested to know if anyone has a solution, or maybe
an idea of a routine that treats this question.
Thanks in advance.
Per
Adding months to a date.
Its straightforward enough; I will dig out a Routine I created for this some time ago. Stay tuned...
(Adding years to a date is even easier.)
-----Original Message-----
From: Per Eriksson [mailto:erper@home.se]
Sent: Tuesday, 15 May 2001 20:22
To: informix-datastage@oliver.com
Subject: Adding months to a date.
Hi,
Ive been using Datastage for a year now on two different projects, and Ive been avoiding the question of adding months to a date, since I have not found the possibility within Datastage. Ive now met the client who is very keen on having the possibility to add months to a date. (Like the command ADD_MONTHS within the SQL language)
Im interested to know if anyone has a solution, or maybe
an idea of a routine that treats this question.
Thanks in advance.
Per
(Adding years to a date is even easier.)
-----Original Message-----
From: Per Eriksson [mailto:erper@home.se]
Sent: Tuesday, 15 May 2001 20:22
To: informix-datastage@oliver.com
Subject: Adding months to a date.
Hi,
Ive been using Datastage for a year now on two different projects, and Ive been avoiding the question of adding months to a date, since I have not found the possibility within Datastage. Ive now met the client who is very keen on having the possibility to add months to a date. (Like the command ADD_MONTHS within the SQL language)
Im interested to know if anyone has a solution, or maybe
an idea of a routine that treats this question.
Thanks in advance.
Per
FUNCTION AddMonths(BaseDate, MonthsOffset)
* Function has two arguments:
* BaseDate (date) the date from which N months is to be calculated
* OffsetMonths (integer) the number of months to add to date (can be
negative)
* Note the following restriction. The usual rules of Iconv() apply. If three
* months are added to "31 Jan 2001", the result is "01 May 2001". This is because
* the routine generates "31 Apr 2001"; when Iconv() is applied to generate an
* internal date format, the date is assumed to be "01 May 2001". This could be
* tested for after the final Iconv(), because the value returned by Status() is 3.
* However, this test is not implemented in this routine.
Equate FunctionName To "AddMonths"
* Initialize return value
Ans = @NULL
* Check the arguments
CheckDate = Oconv(BaseDate, "D")
CheckStatus = Status()
If CheckStatus
Then
Message = "BaseDate argument is not a valid internal format date."
Call DSTransformError(Message, FunctionName)
End
If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
Then
Message = "OffsetMonths argument must be an integer."
Call DSTransformError(Message, FunctionName)
End
* If we reach this point and the variable "Message" is unassigned, we
* have not generated an error message, so can proceed.
If Unassigned(Message)
Then
* Generate a known external form of the date (YYYY-MM-DD)
ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
ExtYears = ExtDate[1,4]
ExtMonth = ExtDate[6,2]
ExtDay = ExtDate[9,2]
* Extract the sign and absolute value of OffsetMonths
SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
AbsMonths = Abs(OffsetMonths)
Years = Int(AbsMonths / 12)
Months = Mod(AbsMonths, 12)
* Add years
ExtYears += (Years * SignMonths)
* Make adjustments
If SignMonths < 0
Then
If AbsMonths > ExtMonth
Then
ExtYears -= 1
ExtMonth += 12
End
End
Else
If ExtMonth + Months > 12
Then
ExtYears += 1
ExtMonth -= 12
End
End
* Add months
ExtMonth += (Months * SignMonths)
* Re-construct external date
ExtDate["-",1,1] = ExtYears
ExtDate["-",2,1] = ExtMonth
* Convert to internal format to return value
Ans = Iconv(ExtDate, "DYMD")
$IFDEF Testing
Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
Call DSTransformError(Message, "Result")
$ENDIF
End
RETURN(Ans)
* Function has two arguments:
* BaseDate (date) the date from which N months is to be calculated
* OffsetMonths (integer) the number of months to add to date (can be
negative)
* Note the following restriction. The usual rules of Iconv() apply. If three
* months are added to "31 Jan 2001", the result is "01 May 2001". This is because
* the routine generates "31 Apr 2001"; when Iconv() is applied to generate an
* internal date format, the date is assumed to be "01 May 2001". This could be
* tested for after the final Iconv(), because the value returned by Status() is 3.
* However, this test is not implemented in this routine.
Equate FunctionName To "AddMonths"
* Initialize return value
Ans = @NULL
* Check the arguments
CheckDate = Oconv(BaseDate, "D")
CheckStatus = Status()
If CheckStatus
Then
Message = "BaseDate argument is not a valid internal format date."
Call DSTransformError(Message, FunctionName)
End
If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
Then
Message = "OffsetMonths argument must be an integer."
Call DSTransformError(Message, FunctionName)
End
* If we reach this point and the variable "Message" is unassigned, we
* have not generated an error message, so can proceed.
If Unassigned(Message)
Then
* Generate a known external form of the date (YYYY-MM-DD)
ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
ExtYears = ExtDate[1,4]
ExtMonth = ExtDate[6,2]
ExtDay = ExtDate[9,2]
* Extract the sign and absolute value of OffsetMonths
SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
AbsMonths = Abs(OffsetMonths)
Years = Int(AbsMonths / 12)
Months = Mod(AbsMonths, 12)
* Add years
ExtYears += (Years * SignMonths)
* Make adjustments
If SignMonths < 0
Then
If AbsMonths > ExtMonth
Then
ExtYears -= 1
ExtMonth += 12
End
End
Else
If ExtMonth + Months > 12
Then
ExtYears += 1
ExtMonth -= 12
End
End
* Add months
ExtMonth += (Months * SignMonths)
* Re-construct external date
ExtDate["-",1,1] = ExtYears
ExtDate["-",2,1] = ExtMonth
* Convert to internal format to return value
Ans = Iconv(ExtDate, "DYMD")
$IFDEF Testing
Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
Call DSTransformError(Message, "Result")
$ENDIF
End
RETURN(Ans)
Ray,
Just looking at the AddMonths function and I could not see where ExtDay is used when re-constructing the date. Could you please clarify?
Stephen Ormsby
IT Consultant
Black Diamond
T e c h n o l o g i e s
Level 3, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: stephen.ormsby@bdt.com.au
Telephone: (03) 9686 - 0055
Facsimile: (03) 9696 - 6335
Mobile: 0412 379 120
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
> -----Original Message-----
> From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
> Sent: Wednesday, 16 May 2001 4:34 pm
> To: informix-datastage@oliver.com
> Subject: RE: Adding months to a date.
>
> FUNCTION AddMonths(BaseDate, MonthsOffset)
>
> * Function has two arguments:
> * BaseDate (date) the date from which N months is to be
> calculated
> * OffsetMonths (integer) the number of months to add to date (can
> be
> negative)
>
> * Note the following restriction. The usual rules of Iconv() apply.
> If three
> * months are added to "31 Jan 2001", the result is "01 May 2001".
> This is because
> * the routine generates "31 Apr 2001"; when Iconv() is applied to
> generate an
> * internal date format, the date is assumed to be "01 May 2001". This
> could be
> * tested for after the final Iconv(), because the value returned by
> Status()
> is 3.
> * However, this test is not implemented in this routine.
>
> Equate FunctionName To "AddMonths"
>
> * Initialize return value
> Ans = @NULL
>
> * Check the arguments
> CheckDate = Oconv(BaseDate, "D")
> CheckStatus = Status()
> If CheckStatus
> Then
> Message = "BaseDate argument is not a valid internal format
> date."
> Call DSTransformError(Message, FunctionName)
> End
>
> If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
> Then
> Message = "OffsetMonths argument must be an integer."
> Call DSTransformError(Message, FunctionName)
> End
>
> * If we reach this point and the variable "Message" is unassigned, we
> * have not generated an error message, so can proceed.
>
> If Unassigned(Message)
> Then
>
> * Generate a known external form of the date (YYYY-MM-DD)
> ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
> ExtYears = ExtDate[1,4]
> ExtMonth = ExtDate[6,2]
> ExtDay = ExtDate[9,2]
>
> * Extract the sign and absolute value of OffsetMonths
> SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
> AbsMonths = Abs(OffsetMonths)
> Years = Int(AbsMonths / 12)
> Months = Mod(AbsMonths, 12)
>
> * Add years
> ExtYears += (Years * SignMonths)
>
> * Make adjustments
> If SignMonths < 0
> Then
> If AbsMonths > ExtMonth
> Then
> ExtYears -= 1
> ExtMonth += 12
> End
> End
> Else
> If ExtMonth + Months > 12
> Then
> ExtYears += 1
> ExtMonth -= 12
> End
> End
>
> * Add months
> ExtMonth += (Months * SignMonths)
>
> * Re-construct external date
> ExtDate["-",1,1] = ExtYears
> ExtDate["-",2,1] = ExtMonth
>
> * Convert to internal format to return value
> Ans = Iconv(ExtDate, "DYMD")
>
> $IFDEF Testing
> Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
> Call DSTransformError(Message, "Result")
> $ENDIF
>
> End
>
> RETURN(Ans)
Just looking at the AddMonths function and I could not see where ExtDay is used when re-constructing the date. Could you please clarify?
Stephen Ormsby
IT Consultant
Black Diamond
T e c h n o l o g i e s
Level 3, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: stephen.ormsby@bdt.com.au
Telephone: (03) 9686 - 0055
Facsimile: (03) 9696 - 6335
Mobile: 0412 379 120
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
> -----Original Message-----
> From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
> Sent: Wednesday, 16 May 2001 4:34 pm
> To: informix-datastage@oliver.com
> Subject: RE: Adding months to a date.
>
> FUNCTION AddMonths(BaseDate, MonthsOffset)
>
> * Function has two arguments:
> * BaseDate (date) the date from which N months is to be
> calculated
> * OffsetMonths (integer) the number of months to add to date (can
> be
> negative)
>
> * Note the following restriction. The usual rules of Iconv() apply.
> If three
> * months are added to "31 Jan 2001", the result is "01 May 2001".
> This is because
> * the routine generates "31 Apr 2001"; when Iconv() is applied to
> generate an
> * internal date format, the date is assumed to be "01 May 2001". This
> could be
> * tested for after the final Iconv(), because the value returned by
> Status()
> is 3.
> * However, this test is not implemented in this routine.
>
> Equate FunctionName To "AddMonths"
>
> * Initialize return value
> Ans = @NULL
>
> * Check the arguments
> CheckDate = Oconv(BaseDate, "D")
> CheckStatus = Status()
> If CheckStatus
> Then
> Message = "BaseDate argument is not a valid internal format
> date."
> Call DSTransformError(Message, FunctionName)
> End
>
> If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
> Then
> Message = "OffsetMonths argument must be an integer."
> Call DSTransformError(Message, FunctionName)
> End
>
> * If we reach this point and the variable "Message" is unassigned, we
> * have not generated an error message, so can proceed.
>
> If Unassigned(Message)
> Then
>
> * Generate a known external form of the date (YYYY-MM-DD)
> ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
> ExtYears = ExtDate[1,4]
> ExtMonth = ExtDate[6,2]
> ExtDay = ExtDate[9,2]
>
> * Extract the sign and absolute value of OffsetMonths
> SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
> AbsMonths = Abs(OffsetMonths)
> Years = Int(AbsMonths / 12)
> Months = Mod(AbsMonths, 12)
>
> * Add years
> ExtYears += (Years * SignMonths)
>
> * Make adjustments
> If SignMonths < 0
> Then
> If AbsMonths > ExtMonth
> Then
> ExtYears -= 1
> ExtMonth += 12
> End
> End
> Else
> If ExtMonth + Months > 12
> Then
> ExtYears += 1
> ExtMonth -= 12
> End
> End
>
> * Add months
> ExtMonth += (Months * SignMonths)
>
> * Re-construct external date
> ExtDate["-",1,1] = ExtYears
> ExtDate["-",2,1] = ExtMonth
>
> * Convert to internal format to return value
> Ans = Iconv(ExtDate, "DYMD")
>
> $IFDEF Testing
> Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
> Call DSTransformError(Message, "Result")
> $ENDIF
>
> End
>
> RETURN(Ans)
Its not used, Stephen. It was only added for testing purposes. I didnt remove it afterwards.
When the date is reconstructed near the end, the code uses delimited substring replacement. The original design reconstructed the date using ExtDate = ExtYears : "-" : ExtMonth : "-" : ExtDay
Regards,
Ray
-----Original Message-----
From: Ormsby, Stephen [mailto:Stephen.Ormsby@team.telstra.com]
Sent: Thursday, 17 May 2001 07:59
To: informix-datastage@oliver.com
Subject: RE: Adding months to a date.
Ray,
Just looking at the AddMonths function and I could not see where ExtDay is used when re-constructing the date. Could you please clarify?
Stephen Ormsby
IT Consultant
Black Diamond
T e c h n o l o g i e s
Level 3, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: stephen.ormsby@bdt.com.au
Telephone: (03) 9686 - 0055
Facsimile: (03) 9696 - 6335
Mobile: 0412 379 120
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
> -----Original Message-----
> From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
> Sent: Wednesday, 16 May 2001 4:34 pm
> To: informix-datastage@oliver.com
> Subject: RE: Adding months to a date.
>
> FUNCTION AddMonths(BaseDate, MonthsOffset)
>
> * Function has two arguments:
> * BaseDate (date) the date from which N months is to be
> calculated
> * OffsetMonths (integer) the number of months to add to date (can
> be
> negative)
>
> * Note the following restriction. The usual rules of Iconv() apply.
> If three
> * months are added to "31 Jan 2001", the result is "01 May 2001".
> This is because
> * the routine generates "31 Apr 2001"; when Iconv() is applied to
> generate an
> * internal date format, the date is assumed to be "01 May 2001". This
> could be
> * tested for after the final Iconv(), because the value returned by
> Status()
> is 3.
> * However, this test is not implemented in this routine.
>
> Equate FunctionName To "AddMonths"
>
> * Initialize return value
> Ans = @NULL
>
> * Check the arguments
> CheckDate = Oconv(BaseDate, "D")
> CheckStatus = Status()
> If CheckStatus
> Then
> Message = "BaseDate argument is not a valid internal format
> date."
> Call DSTransformError(Message, FunctionName)
> End
>
> If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
> Then
> Message = "OffsetMonths argument must be an integer."
> Call DSTransformError(Message, FunctionName)
> End
>
> * If we reach this point and the variable "Message" is unassigned, we
> * have not generated an error message, so can proceed.
>
> If Unassigned(Message)
> Then
>
> * Generate a known external form of the date (YYYY-MM-DD)
> ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
> ExtYears = ExtDate[1,4]
> ExtMonth = ExtDate[6,2]
> ExtDay = ExtDate[9,2]
>
> * Extract the sign and absolute value of OffsetMonths
> SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
> AbsMonths = Abs(OffsetMonths)
> Years = Int(AbsMonths / 12)
> Months = Mod(AbsMonths, 12)
>
> * Add years
> ExtYears += (Years * SignMonths)
>
> * Make adjustments
> If SignMonths < 0
> Then
> If AbsMonths > ExtMonth
> Then
> ExtYears -= 1
> ExtMonth += 12
> End
> End
> Else
> If ExtMonth + Months > 12
> Then
> ExtYears += 1
> ExtMonth -= 12
> End
> End
>
> * Add months
> ExtMonth += (Months * SignMonths)
>
> * Re-construct external date
> ExtDate["-",1,1] = ExtYears
> ExtDate["-",2,1] = ExtMonth
>
> * Convert to internal format to return value
> Ans = Iconv(ExtDate, "DYMD")
>
> $IFDEF Testing
> Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
> Call DSTransformError(Message, "Result")
> $ENDIF
>
> End
>
> RETURN(Ans)
When the date is reconstructed near the end, the code uses delimited substring replacement. The original design reconstructed the date using ExtDate = ExtYears : "-" : ExtMonth : "-" : ExtDay
Regards,
Ray
-----Original Message-----
From: Ormsby, Stephen [mailto:Stephen.Ormsby@team.telstra.com]
Sent: Thursday, 17 May 2001 07:59
To: informix-datastage@oliver.com
Subject: RE: Adding months to a date.
Ray,
Just looking at the AddMonths function and I could not see where ExtDay is used when re-constructing the date. Could you please clarify?
Stephen Ormsby
IT Consultant
Black Diamond
T e c h n o l o g i e s
Level 3, 1 Southbank Boulevard,
Southbank, Victoria 3006
E-mail: stephen.ormsby@bdt.com.au
Telephone: (03) 9686 - 0055
Facsimile: (03) 9696 - 6335
Mobile: 0412 379 120
-------INTERNET E-MAIL CONFIDENTIALITY/DISCLAIMER--------
Privileged and confidential information may be contained in this e-mail. If you are not the intended recipient of this communication please delete and destroy all copies and kindly notify the sender by return e-mail. Recipients of this e-mail must not use, disclose or forward any information or attachments without express permission from Black Diamond Technologies.
Any views expressed in this communication are those of the individual sender except where the sender specifically states them to be the views of Black Diamond Technologies. Except as required at law, we do not represent warrant and/or guarantee that the integrity of this communication has been maintained or that it is free of errors, viruses, interception or interference.
> -----Original Message-----
> From: Ray Wurlod [SMTP:ray.wurlod@Informix.Com]
> Sent: Wednesday, 16 May 2001 4:34 pm
> To: informix-datastage@oliver.com
> Subject: RE: Adding months to a date.
>
> FUNCTION AddMonths(BaseDate, MonthsOffset)
>
> * Function has two arguments:
> * BaseDate (date) the date from which N months is to be
> calculated
> * OffsetMonths (integer) the number of months to add to date (can
> be
> negative)
>
> * Note the following restriction. The usual rules of Iconv() apply.
> If three
> * months are added to "31 Jan 2001", the result is "01 May 2001".
> This is because
> * the routine generates "31 Apr 2001"; when Iconv() is applied to
> generate an
> * internal date format, the date is assumed to be "01 May 2001". This
> could be
> * tested for after the final Iconv(), because the value returned by
> Status()
> is 3.
> * However, this test is not implemented in this routine.
>
> Equate FunctionName To "AddMonths"
>
> * Initialize return value
> Ans = @NULL
>
> * Check the arguments
> CheckDate = Oconv(BaseDate, "D")
> CheckStatus = Status()
> If CheckStatus
> Then
> Message = "BaseDate argument is not a valid internal format
> date."
> Call DSTransformError(Message, FunctionName)
> End
>
> If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
> Then
> Message = "OffsetMonths argument must be an integer."
> Call DSTransformError(Message, FunctionName)
> End
>
> * If we reach this point and the variable "Message" is unassigned, we
> * have not generated an error message, so can proceed.
>
> If Unassigned(Message)
> Then
>
> * Generate a known external form of the date (YYYY-MM-DD)
> ExtDate = Oconv(BaseDate, "D-YMD[4,2,2]")
> ExtYears = ExtDate[1,4]
> ExtMonth = ExtDate[6,2]
> ExtDay = ExtDate[9,2]
>
> * Extract the sign and absolute value of OffsetMonths
> SignMonths = (If OffsetMonths < 0 Then -1 Else +1)
> AbsMonths = Abs(OffsetMonths)
> Years = Int(AbsMonths / 12)
> Months = Mod(AbsMonths, 12)
>
> * Add years
> ExtYears += (Years * SignMonths)
>
> * Make adjustments
> If SignMonths < 0
> Then
> If AbsMonths > ExtMonth
> Then
> ExtYears -= 1
> ExtMonth += 12
> End
> End
> Else
> If ExtMonth + Months > 12
> Then
> ExtYears += 1
> ExtMonth -= 12
> End
> End
>
> * Add months
> ExtMonth += (Months * SignMonths)
>
> * Re-construct external date
> ExtDate["-",1,1] = ExtYears
> ExtDate["-",2,1] = ExtMonth
>
> * Convert to internal format to return value
> Ans = Iconv(ExtDate, "DYMD")
>
> $IFDEF Testing
> Message = "Generated date = ":Quote(Oconv(Ans,"D-YMD[4,2,2]"))
> Call DSTransformError(Message, "Result")
> $ENDIF
>
> End
>
> RETURN(Ans)
Hi again,
Thanks for all Your ideas! I liked the idea of having an e- mail sent when a job aborts. Ill have to try that some
day...
A special thanks for the help with the routine for dates
Ray.
Weve been changing it a bit to make it act like the
Oracle "AddMonths", thus adding a clause to test if date is
last day of month etc.
Weve also changed the input since we preferred to use
TIMESTAMP, but thats a minor change.
Im sending You the "new" routine in case someone else
would need it...
Thanks again
Per
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
--
*Returns the date d (Arg1) plus n (Arg2) months. d should
be in TIMESTAMP format. The argument n can be any integer.
*If d is the last day of the month
* or if the resulting month has fewer days than the day
component of d then
* the result is the last day of the resulting month
*Otherwise,
* the result has the same day component as d
Equate FunctionName To "AddMonths"
* Initialize return value
Ans = @NULL
* Initialize variables
BaseDate = Arg1
OffsetMonths = Arg2
* Check the arguments
DatePart = MatchField(BaseDate,"0X 2N:2N:2N",1)
If DatePart = ""
Then
Message = "Invalid timestamp input"
Call DSTransformError(Message, FunctionName)
End
* Convert a TIMESTAMP to an Internal Date.
* Reply = Iconv(DatePart,"D-YMD[4,2,2]")
If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
Then
Message = "OffsetMonths argument must be an
integer."
Call DSTransformError(Message, FunctionName)
End
* If we reach this point and the variable "Message" is
unassigned, we
* have not generated an error message, so can proceed.
If Unassigned(Message)
Then
* Generate a known external form of the date (YYYY-MM-DD)
ExtDate = BaseDate
ExtYears = ExtDate[1,4]
ExtMonth = ExtDate[6,2]
ExtDay = ExtDate[9,2]
ExtTime = ExtDate[11,9]
* Add Relativ Months
TargetMonth = ExtMonth + OffsetMonths
* Extract the sign and absolute value of TargetMonth
SignMonths = (If TargetMonth > 0 Then +1 Else -1)
AddYears = Int(TargetMonth / 12)
Month = Mod(TargetMonth, 12)
* Correct Int & Mod behavior if TargetMonth is negativ or
nul
If (Month
Thanks for all Your ideas! I liked the idea of having an e- mail sent when a job aborts. Ill have to try that some
day...
A special thanks for the help with the routine for dates
Ray.
Weve been changing it a bit to make it act like the
Oracle "AddMonths", thus adding a clause to test if date is
last day of month etc.
Weve also changed the input since we preferred to use
TIMESTAMP, but thats a minor change.
Im sending You the "new" routine in case someone else
would need it...
Thanks again
Per
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
--
*Returns the date d (Arg1) plus n (Arg2) months. d should
be in TIMESTAMP format. The argument n can be any integer.
*If d is the last day of the month
* or if the resulting month has fewer days than the day
component of d then
* the result is the last day of the resulting month
*Otherwise,
* the result has the same day component as d
Equate FunctionName To "AddMonths"
* Initialize return value
Ans = @NULL
* Initialize variables
BaseDate = Arg1
OffsetMonths = Arg2
* Check the arguments
DatePart = MatchField(BaseDate,"0X 2N:2N:2N",1)
If DatePart = ""
Then
Message = "Invalid timestamp input"
Call DSTransformError(Message, FunctionName)
End
* Convert a TIMESTAMP to an Internal Date.
* Reply = Iconv(DatePart,"D-YMD[4,2,2]")
If Not(OffsetMonths Matches "1N0N" : @VM : "-1N0N")
Then
Message = "OffsetMonths argument must be an
integer."
Call DSTransformError(Message, FunctionName)
End
* If we reach this point and the variable "Message" is
unassigned, we
* have not generated an error message, so can proceed.
If Unassigned(Message)
Then
* Generate a known external form of the date (YYYY-MM-DD)
ExtDate = BaseDate
ExtYears = ExtDate[1,4]
ExtMonth = ExtDate[6,2]
ExtDay = ExtDate[9,2]
ExtTime = ExtDate[11,9]
* Add Relativ Months
TargetMonth = ExtMonth + OffsetMonths
* Extract the sign and absolute value of TargetMonth
SignMonths = (If TargetMonth > 0 Then +1 Else -1)
AddYears = Int(TargetMonth / 12)
Month = Mod(TargetMonth, 12)
* Correct Int & Mod behavior if TargetMonth is negativ or
nul
If (Month