2017-03-28 03:40 AM
There was a requirement to modify the full date field from YYYY-MM-DD HH:MM:SS:xxx to YYYY-MM-DD. But I don't see date format in data format of that cell properties, only shown is text.
I am using VM capacity DM.
So I was thinking to modify that Full date format when fetching it from DB. To my best knowledge to_date is suitable for that. to_date is not there in the functions list of data item expressions.
I checked if that is available to use in report studio and I can see its help (as shown 1 in attachment). But when I use it as proper function SQL throw error as to_date does not exist (as shown 2 in attachment).
1. Can someone confirm if there is error in my syntax or what?
2. Where can I get entire list of functions supported by OCI? I am not talking about Cognos Report Studio as to_date and many other functions are listed in report studio guide but can't be used when creating reports in OCI DWH.
3. Is this the thin version of cognos which is used as backend in OCI?
Solved! SEE THE SOLUTION
2017-03-28 02:09 PM
Sunil, could you include a screenshot? I'd like to know what interface you're using. The Date format options are always present in Report Studio, in my experience.
If the object you're trying to reformat is actually a string and not a date, then to_date would be able to turn it back into a string. There's quite a bit of syntax that goes into to_date. You will probably eventually need to understand whether you're running to_date in MySQL or Cognos, which is kind of a deep topic and definitely depends on where you're working on all this.
The set of functions you can use are constrained by where the function gets evaluated. Some funcitons and syntaxes are valid in Cognos, some in MySQL. OCI doesn't impose any additional limits, beyond what the Cognos-MySQL combo, and whether you're using local or server processing, apply.
2017-03-29 03:21 AM - edited 2017-03-29 03:31 AM
My mistake, forgot to add attachment. Here you go with "to_date error".
Also attaching another screenshot " no_date_format" to show you (1) simple report, (2) no date format is available for full date and (3) result of that report.
I need date as YYYY-MM-DD.
2017-03-29 12:44 PM
When I encounter an error like the one in your first screenshot, I throw it at Google and see what comes back. https://www.google.com/search?q=mysql+to_date&ie=utf-8&oe=utf-8
The second result of that search contrasts the to_date syntax of other databases with the str_to_date syntax of MySQL. The first result is the manual page for str_to_date, the function that you're looking for. Try using str_to_date and see if you can get that to work.
For the issue where you have no date format options, look at the Properties screen and change Source Type from Member Caption to Data Item Value. Now you can format it as a date. This is the fix you're looking for; changing the type of the data item value probably won't have any impact on the data type of its label.
2017-03-31 03:10 AM
Thanks for the pointer.
Now I know my mistake and how to fix it. Even that 'YYYY-MM-DD' syntax was wrong after I read the mysql to_date. I have to use the '%Y-%m-%d'.
Also tested converting source type to data item value, that also allowed me to change data type to Date.
Appreciate your help. Thanks.