Math on date values

[spracht]

Hello,

I sometimes have to do something like that:

startdate=iconv('2000-01-01', "D-YMD")
enddate=iconv('2002-12-01', "D-YMD")
currdate=startdate

loop while currdate

[kcbland]

Unless Ray says so, there is no inherent function for adding months to a date. Your best option is to write a DS function to do so, then reuse it everywhere you need the logic. It should be transparent to your DS designs where the function originated: internal BASIC function, SDK supplied function, or User-defined function.

I don't know uv SQL well enough to offer a recommendation, because SQL is not the best way to use a hash file. It's up to you if you want to tell me why you're doing this, because I could probably offer you a better solution than using a DataStage hash file as a relational data store.

Good luck!

Kenneth Bland

[ray.wurlod]

Here's one I baked earlier. It calculates the number of completed months between two dates, so is not an answer to this post; rather it demonstrates techniques. You've pretty much got AddMonths nailed, though perhaps not optimally. For example, if DeltaMonths is >= 12 you could add (12*Int(DeltaMonths/12)) months then add (Mod(DeltaMonths,12)) months. Also, what happens when you want to add 13 months to 31 Jan 2003? This is handled by specifying one or more business rules, and coding to these.

FUNCTION CompletedMonths(Date1,Date2)
$COPYRIGHT "Copyright (c) 2003, Ray Wurlod. All rights reserved."
*
* Purpose
* Returns the number of completed months between Date1 and Date2.
*
* History
* Date..... Version Programmer.... Details of Modification
* 26 Jul 03 2.0.0 Ray Wurlod Initial coding
*

Equate RoutineName To "CompletedMonths"

* Modify the following constant for other languages.
Equate ErrorMessage1 To "Input argument is not a valid internal format date for this routine."

* Date1 and Date2 must be valid internal format dates. In this routine
* a valid internal format date is an integer with five or fewer digits.
* This means that dates earlier than 17 Mar 1694 or later than 14 Oct 2241
* will be rejected. It is not felt that this is a major limitation.
* To bypass testing for this, undefine the following token.
$DEFINE TestDates

* The following token defines whether a null result will be reported as
* a "true" null or as the string "" for display in the Test grid.
$UNDEFINE TestNull

* Initialize result assuming that there will be an error of some kind.
Ans = @NULL

$IFDEF TestDates
TestDate1 = Oconv(Date1,"R-99999,99999")
TestDate2 = Oconv(Date2,"R-99999,99999")
If Not(Len(TestDate1)) And Not(Len(TestDate2))
Then
Call DSTransformError(ErrorMessage1, RoutineName)
GoTo MainExit
End
$ENDIF

* If Date1 > Date2 then the result is zero.
If Date1 > Date2
Then
Ans = 0
GoTo MainExit
End

* Generate external formats and decompose.
ExtDate1 = Oconv(Date1, "D-YMD[4,2,2]")
Year1 = ExtDate1[1,4]
Month1 = ExtDate1[6,2]
Day1 = ExtDate1[9,2]
ExtDate2 = Oconv(Date2, "D-YMD[4,2,2]")
Year2 = ExtDate2[1,4]
Month2 = ExtDate2[6,2]
Day2 = ExtDate2[9,2]

* Calculated completed months based on decomposed dates
YearDiff = Year2 - Year1
MonthDiff = Month2 - Month1
If MonthDiff < 0
Then
MonthDiff += 12
YearDiff -= 1
End
DayDiff = Day2 - Day1

CompletedMonths = 12 * YearDiff + MonthDiff
If DayDiff < 0
Then
CompletedMonths -= 1
End

Ans = CompletedMonths

MainExit:
$IFDEF TestNull
If IsNull(Ans)
Then
Ans = ""
End
$ENDIF

RETURN(Ans)


UniVerse (DataStage) SQL does not have any date manipulation functions other than the CURRENT_DATE and CURRENT_TIME keywords. Because internal dates are stored as integers it is sufficient to compare them (as number of days difference) by simple subtraction in an virtual column. Anything more complex, such as completed months, can be done by using a virtual column that invokes an appropriate function. For example, to use the above function:

SELECT Date1, Date2, EVAL "SUBR('DSU.CompletedMonths',Date1,Date2)"
FROM tablename ... ;


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[Optico]

Hi.

We have a number of date routines cooked up if you are interested.

For your problem you could call our routine DateAdd like this : DateAdd(Date as timestamp, "MON", 1) And you would get your new date as a reply.

Drop us a line if you are interested.

The routines are a bit large to include in a posting.



B. Sorensen,
Optico IT ApS