Date format - Invalid month

[cognosun]

We don't have date format field in our schema, but 2 seperate fields one for 'year' which is a number and 'month id' field as an integer.

But we need date field for our reports ( mm-yyyy)

We used _make_timestamp function to create a timesptamp ( by passing above 2 fields) and later did a cast to it to convert to date. It was working fine then, but all of a sudden it's throwing us error only in Report studio.

This newly defined date field is working fine in FM.

The time we run it in Report studio, it's giving "invalid month" error

[blom0344]

any chance you are trying to pass a month value that does not fit (other than 1 ,2,3,...,12) ? For instance a zero (0) for the ID?

[wyconian]

Something else to think about; if you want the date format as mm-yyyy you need the date to be like 01, 02, 03..12 not just 1,2,3..12

[RKMI]

Hi Cognosun,

Try this, Create two data items as follows;
1. Month Data-item: if (extract(month,[Date/Time]) <10)
then
('0'+cast(extract(month,[Date/Time]),char(1)))
else
(cast(extract(month,[Date/Time]),char(2)))

2. Month-Year Data-item: [Month]+'-' +cast(extract(year,[Date/Time]),varchar(4))

I think this should give you the format of mm-yyyy, I have used this logic in a relational model and works.

Thanks,
RK

[cognosun]

Thanks for your replies.

I've altogether created a new column using to_date function which is working fine in FM and RS.

1) I named months by mapping numerics to names
2) Created new date field using....To_date(year||' '||monthid, 'MM/YYYY')
3) As To_date will retun a timestamp format date....at RS level I've changed it's date format

it's working as expected :-)