If your variable is a date you can't define a special format for it, it's a date!
If you want a special format (like YYYY-MM-DD) and want to always be sure that your variable has that format you need to get the value as formatted text directly instead of a date, so use something like TO_CHAR(your_date, 'yyyy-mm-dd') .
Thanks for a reply what I not sure of is the syntax to this. Please assist.
If I understand you correctly, you mean to format this in the repository manager?
If so, I think this will create problems when I use it on the filter
A repository variable is set by an init block, so open your init block and adapt the query populating the variable. It's normal SQL, so you are supposed to be able to change it easily ...
And it's impossible to help you without knowing what is the connection pool connected with (Oracle, SQL, DB2 or anything else) and how does the query looks like.
As said: in Oracle it would be something like TO_CHAR(your_date, 'yyyy-mm-dd')
Where are you using the variable? In which place are you trying to format it?
Analysis formula, static text or where?
If it's in a column of an analysis you simply format your column with the property of the column like any other analysis column, if you access it directly in a text view you can't (=> format it as text in the RPD)
Im using this variable as dashboard prompt and as well as in the static text. Im using Oracle DB as my source.
i have an Opening Balance and Movements of Stock in between and a Closing Balance in my report. So Opening balance is < FINANCIAL_YEAR_DATE and Movements of stock is >= FINANCIAL_YEAR_DATE and <= CURRENT_DATE.
And I want to dispay this on the report and schedule an Agent and email it to users.
For filtering or setting default value of a prompt you need to keep it as a date. For display the problem with a date is that you can't always easily format it the way you want, so often what you can do is to have 2 variables: one is a date used for calculations, filters and that kind of things, the second is a varchar of the same value formatted in the way you want.