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!
Calculate number of months between 2 dates
Moderators: chulett, rschirm, roy
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
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
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
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
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
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