Fiscal Dates and last_day

[system]

Hello,
I am trying to create a date object in a universe from “fiscal month” field and a “fiscal year” field.

I can successfully create a date if I select a day 1 through 28, but i would like to choose the last day of the month, and i can’t get the last_day function in Oracle 10g to work.
This is the select statement that works, but only for days 1 - 28,

to_date(@select(table\fiscal month) || ‘01’ || @select(table\fiscal year), ‘MM/DD/YYYY’)

I was trying to get the last day of the month by:

last_day(to_date(@select(table\fiscal month) || ‘01’ || @select(table\fiscal year), ‘MM/DD/YYYY’))

When I try to display a list of values, it says that date is the wrong data type, i tried all the data types and none of them worked. When I tried to make a report, it said invalid date.

Please help.

Thank you,
Trish

---

twatson (BOB member since 2008-10-07)

[system]

Hi,

What data types are these objects of?
@select(table\fiscal month)
@select(table\fiscal year)
Are they numbers or strings?

And can you post a sample of values from both of the objects?

---

Marek Chladny (BOB member since 2003-11-27)

[system]

@twatson

to_date(@select(table\fiscal month) || ‘01’ || @select(table\fiscal year), ‘MM/DD/YYYY’)
Trish

I think you are missing ‘/’ before DD and YYYY. Make sure you are using the right data type to convert it correctly.

---

BO_Chief (BOB member since 2004-06-06)

[MarkP]

You have posted:

to_date(@select(table\fiscal month) || '01' || @select(table\fiscal year), 'MM/DD/YYYY')

I suggest:

to_date(@select(table\fiscal month) || '01' || @select(table\fiscal year), "MMDDYYYY") 

Regards,
Mark

[system]

Fiscal Month and Fiscal Year are both numbers. The values are:

Year:
2008, 2009, 2010

AND

Month:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

The to_date function works either way using ‘MMDDYYYY’ or 'MM/DD/YYYY, it is the Last_day function that is causing me problems.
It is strange becuase it parses fine, but thows the “Error during SQL executionL (DA0003)”, & “Exception: DBD, ORA-01843: not a valid month
State: N/A” when trying to display values, or run a report.

This is the formula I am having problems with:
last_day(to_date(@Select(Tfacr200\Fiscal Month) ||‘28’|| @Select(Tfacr200\Fiscal Year),‘MM/DD/YYYY’))

Thanks again for your help.
Trish

---

twatson (BOB member since 2008-10-07)

[MarkP]

@twatson

Fiscal Month and Fiscal Year are both numbers. The values are:

Year:
2008, 2009, 2010

AND

Month:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

The to_date function works either way using ‘MMDDYYYY’ or 'MM/DD/YYYY, it is the Last_day function that is causing me problems.
It is strange becuase it parses fine, but thows the “Error during SQL executionL (DA0003)”, & “Exception: DBD, ORA-01843: not a valid month
State: N/A” when trying to display values, or run a report.

This is the formula I am having problems with:
last_day(to_date(@Select(Tfacr200\Fiscal Month) ||‘28’|| @Select(Tfacr200\Fiscal Year),‘MM/DD/YYYY’))

Thanks again for your help.
Trish

Have you got your days and months the wrong way round? Works for 6/12 but could also be 12/6 - 13/6 can only work one way for example.