Calculate number of months between 2 dates

nelc · Post by **nelc** » Mon Aug 11, 2003 12:33 am

Hi All,

Need to calculate a duration field, (end date) - (start date).
Result required to be in months.

Couldn't find any appropriate functions to apply.
Anyone can advise?

Thanks in advance!

spracht · Post by **spracht** » Mon Aug 11, 2003 12:48 am

Nelc

Ray kindly posted a user-defined function that calculates the number of completed months between 2 dates in http://www.tools4datastage.com/forum/to ... C_ID=84773

Stephan

anupam · Post by **anupam** » Mon Aug 11, 2003 1:12 am

hi,
You can use
Oconv(Iconv(toTxfm.End_Date,"D-YMD[4,2,2]") - Iconv(toTxfm.Start_Date,"D-YMD[4,2,2]"),"D-M")
to get the Month difference only. For more then 30 days difference you will get 2 as the month difference, and for less then 30 days you will get 1.
The Input Data
Start_Date,End_Date
2003-07-01,2003-08-02
2003-07-02,2003-08-06
2003-07-08,2003-07-26
2003-08-01,2003-09-20
The Output Data
Difference (in Months)
2
2
1
2

I hope this is what you are looking for.

[:)]

Anupam

spracht · Post by **spracht** » Mon Aug 11, 2003 8:15 am

Substracting two dates with a result>0 is the number of days passed since 1967-12-31. Formatting this a "D-M" will, I think, tell you the month in which that day lies. Applied to 2003-08-11 as end-date and 2002-08-10 as start-date, I wouldn't expect 13 (better: 12), but 1 (January 1969).

Stephan

Optico · Post by **Optico** » Wed Sep 17, 2003 10:14 am

This is a somewhat late reply, but hopefully you'll enjoy it even more then

We have built a couple of date-routines that you might like.

Amongst others we have DateDiff and DateAdd routines where the developer can choose to use year, month, day, hour etc. for calling.
All routines use Date/timestamps in DB2-format for input and output exept the two routines TStoDatabase and DatabaseToTS than convert formats.

Drop us a line if you are interested.

quote:Originally posted by nelc
[br]Hi All,

Need to calculate a duration field, (end date) - (start date).
Result required to be in months.

Couldn't find any appropriate functions to apply.
Anyone can advise?

Thanks in advance!

B. Sorensen,
Optico IT ApS