Issue With DateoffSetByComponents Function

[jerome_rajan]

I've been playing around with the DateOffsetByComponents() function to implement one of our business requirements. However, the function seems to be returning incorrect values. Either that or my understanding is wrong.

Code: Select all

DateOffsetByComponents("2013-03-31",0,-1,0

returns 2013-03-03. Any idea why?

[ray.wurlod]

Missing right parenthesis?

I think you may have bumped into a bug. Certainly what you're doing accords with the Information Center documentation for this function.

[ray.wurlod]

The following works.

Code: Select all

DateOffsetByComponents(StringToDate("2013-03-31"),0,-2,0)

but this one gives the same results you found.

Code: Select all

DateOffsetByComponents(StringToDate("2013-03-31"),0,-1,0)

Maybe -1 is a special number - like, you know, i-squared.

[ray.wurlod]

Using job parameters rather than constants made no difference.

Code: Select all

DateOffsetByComponents(StringToDate("2013-03-31"),jpYearOffset,jpMonthOffset,jpDayOffset)

[ray.wurlod]

I have a theory about what this function is doing. Having pondered this theory, I am forced to wonder how they could have done it any other way.

The problem is that not every month has 31 days.

It seems (from other tests such as DateOffsetByComponents("2013-03-31", 0, 1, 0), which returns "2013-05-01") that it calculates forward by the month day number in the source date from the first of the month calculated by the month offset figure.

So, in the original post, the calculation is 31 days forward from "2013-02-01", which is "2013-03-03".

How would YOU specify the function's behaviour? What do you believe the function should return from the original example?

[jerome_rajan]

I expected it to return a date that would ideally be in the month of february. I find it difficult to comprehend the rationale behind why the function would be designed to work the way you think it actually is.

[ray.wurlod]

How would YOU specify the function's behaviour? What do you believe the function should return from the original example?

There is no 31st of February.

[jerome_rajan]

I would want it to take me to the last day of the last month. ie. If my business date is 2013-03-31, then the output should be 2013-02-28. If 2013-03-30, then 2013-02-27.

[ray.wurlod]

So what if it's 2013-02-28? Do you want it to take you to the last day of the previous month (2013-01-31) or the corresponding day number (2013-01-28)? Now do you see the dilemma?

[jerome_rajan]

It should perhaps be subtracting the number of days in the month the specified date is in. So if the date is May 20, it should subtract 31 days and if the date is June 12, then it should subtract 30 days. If the date is in February, it should subtract 28/29 days depending on the year of date.Your thoughts?

[ray.wurlod]

I agree with how it's currently implemented.

You are concentrating on the last day of the month only. There is scope for a different function to do what you do.

(A thought - I wonder if these functions work with other calendars, such as lunar calendars?)