I need to cast timestamp in the to_char(timestamp, 'mm/dd/yyyy hh:mm AM') format. While creating dataset in PI Data Services, I am getting error because :mm is parsed as string. Is there any another way to parse timestamp in this format?
what is the type of you "timestamp" please? Is that a PITime type please? If so, I think it should be better to transfer it into a String at first. You could try to cast PITime type into PITimeFormat type, and use PITimeFormat.OutputString to get a string. After this, please try your code _char(). If still have any problem, please feel free to post it in here.
What type of dataset is this (PI Calculation, Relational or Web Service)?
It is a Relational dataset and datetime coming from Oracle.
While using to_char(timestamp, 'mm/dd/yyyy hh:mm AM') function is considers ":mm" as a placeholder.
Please, use MI instead of mm. See Table 2-15 Datetime Format Elements: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
Also, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm
I tried both. The query works perfect in Oracle. I am having this issue in PI data services.
When you say that the query works in Oracle, do you mean to say in SQLPlus?
The devil is in the details. Can you please post a screenshot of the working query from SQLPlus?
What's the version of your Oracle Database?
What's the Oracle Client version installed on the server hosting PI WebParts?
What driver and version are you using to connect to Oracle?
By Oracle I mean Oracle SQL Developer. Oracle Client 18.104.22.168 on sharepoint server.
Sorry, I only knew SQLPlus. However, if your query works within a native client but you get an error when using it with a PI Data Services relational Dataset, the issue is likely somewhere in between or with PI Data Services. With DataTime conversions, Regional settings may have an impact or the driver you are using to access the database. Please check with your relational Dataset definition for the name of the "Data Source", navigate to Data Sources -> Relational and see how the Relational Data Source is defined. Is this an ODBC, OLEDB or "native" Oracle driver? Who is the provider and what's the driver version? How does the connection string look like?
To see if the issue is with PI Data Services or i.e. with the driver, you can use another client, set up a similar connection using the same driver and see if your query executes just fine.
Looking at the screenshot you've posted, I see a format string that differs from the one you originally posted. Please make sure you are using the exact same query as with Oracle SQL Developer.
Retrieving data ...