DATE(CURRENT_DAY_OF_MONTH, CURRENT_MONTH-1, CURRENT_YEAR )

ACDC

I am using this process to create 24 objects which represent the last 24 months working backwards from the Current Date

CREATE Chart WITH Chart.Month=DATE(CURRENT_DAY_OF_MONTH, CURRENT_MONTH-1, CURRENT_YEAR )
CREATE Chart WITH Chart.Month=DATE(CURRENT_DAY_OF_MONTH, CURRENT_MONTH-2, CURRENT_YEAR )
CREATE Chart WITH Chart.Month=DATE(CURRENT_DAY_OF_MONTH, CURRENT_MONTH-3, CURRENT_YEAR )
CREATE Chart WITH Chart.Month=DATE(CURRENT_DAY_OF_MONTH, CURRENT_MONTH-4, CURRENT_YEAR ) ..........etc etc etc 24 x

This works fine except when it gets to the - 12 Month the Date becomes "NULL"

Thereafter it continues to create the rest of objects as expected

Can anyone figure out why this would break on the -12 month - Is this a bug with the Function , is there a work around ?

Thanks

Rennur

Thinking out loud here:

What happens when the CURRENT_DAY_OF_MONTH=31 AND CURRENT_MONTH=MARCH minus (-) 1.

Wouldn't that make the 31st Feb 2012?

juicer

ACDC,

If you run your process in December, CURRENT_MONTH returns 12 and subtracting 12 gives 0 which is an invalid month for the DATE function.

Try using the MONTH_ADD function like this:
CREATE Chart WITH Chart.Month=MONTH_ADD(CURRENT_DATE,-1)

Note to support (or anyone else who can help)
I also tried to solve this using:
CREATE Chart FOR EACH MONTH BETWEEN CURRENT_DATE AND MONTH_ADD(CURRENT_DATE,-12) WITH Chart.Month=LOOP_ITERATION

But I could not get this to work. I don't know if LOOP_ITERATION is correct and I don't know if it returns a DATE or MONTH. I tried several variations, but nothing worked and the documentation is lacking.

juicer

I figured out what I was doing wrong, I just needed to switch my dates around.

ACDC, you can get what you want in the following line of code:

CREATE Chart FOR EACH MONTH BETWEEN MONTH_ADD(CURRENT_DATE,-24) AND MONTH_ADD(CURRENT_DATE,-1) WITH Chart.Month=LOOP_ITERATION

FYI: in this instance, LOOP_ITERATION returns a date.

ACDC

juicer,
thanks very much for the help