Hi All,
I have a text file which containts values for 2 dates.say like PDATE , SDATE(Purchase Date,Service Date) and stored as timestamp format(eg '2002-04-20 00:00:00:000', '2003-05-22 00:00:00:000' .Now i want to find the Difference between these 2 dates(SDate-PDate) in Months.Can anybody help me how to go about that.
I tried Like
(Oconv(Iconv(Sdate,"D4/YMD"),"D4/YMD) -
Oconv(Iconv(Pdate,"D4/YMD"),"D4/YMD)) / 30.0 in a trasformer but it is not working .....
Thanks & Regards,
Deva Rajan
How to find Months between given 2 dates
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A timestamp will not convert properly based on a "D" conversion, which is for pure dates. You can extract the date component of the timestamp in a number of ways, for example via substring or the Left function.
In your example, you are also missing the trailing quote on the conversion specifier for the Oconv functions; I will assume this is mistyping in your post, as it wouldn't compile as is.
In any case, you don't need the Oconv functions. Given that your algorithm is simply to divide the difference in days by 30, and the fact that Iconv returns the date as the integer number of days since 31 Dec 1967, a suitable formula is:
(Iconv(Left(Sdate,10),'DYMD')-Iconv(Left(Pdate,10),'DYMD'))/30
You could also create a routine to calculate completed months more precisely (for example, subtract the years and multiply by 12, and subtract the months and adjust for day number); it now depends on what your actual business rule for "number of months" actually is.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
In your example, you are also missing the trailing quote on the conversion specifier for the Oconv functions; I will assume this is mistyping in your post, as it wouldn't compile as is.
In any case, you don't need the Oconv functions. Given that your algorithm is simply to divide the difference in days by 30, and the fact that Iconv returns the date as the integer number of days since 31 Dec 1967, a suitable formula is:
(Iconv(Left(Sdate,10),'DYMD')-Iconv(Left(Pdate,10),'DYMD'))/30
You could also create a routine to calculate completed months more precisely (for example, subtract the years and multiply by 12, and subtract the months and adjust for day number); it now depends on what your actual business rule for "number of months" actually is.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518