convert system date to timestamp format

Question

I want to pass timestampe to third party server from sysdate. Below is the required format suppose to send as timedate.

2022-09-23T15:38:47.6927242-05:00

I have tried using below to get timestampe

select systimestamp from dual

While i pass this to payload i am getting below error

The server encountered an error processing the request. Please see the <a rel="help-page" href="https://ws.aramex.net/ShippingAPI.V2/Shipping/Service_1_0.svc/Xml/help">service help page</a> for constructing valid requests to the service. The exception message is 'The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://ws.aramex.net/ShippingAPI/v1/:Shipments. The InnerException message was 'There was an error deserializing the object of type System.Collections.Generic.List`1[[Corp.ShippingAPI.FrontEnd.Contracts.Shipment, Corp.ShippingAPI.FrontEnd.Contracts, Version=1.287.0.0, Culture=neutral, PublicKeyToken=null]]. The value '' cannot be parsed as the type 'DateTime'.'.  Please see InnerException for more details.'. See server logs for more details. The exception stack trace is: </p>
      <p>   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameterPart(XmlDictionaryReader reader, PartInfo part, Boolean isRequest)
   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeParameters(XmlDictionaryReader reader, PartInfo[] parts, Object[] parameters, Boolean isRequest)
   at System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.DeserializeBody(XmlDictionaryReader reader, MessageVersion version, String action, MessageDescription messageDescription, Object[] parameters, Boolean isRequest)
   at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeBodyContents(Message message, Object[] parameters, Boolean isRequest)
   at System.ServiceModel.Dispatcher.OperationFormatter.DeserializeRequest(Message message, Object[] parameters)
   at System.ServiceModel.Dispatcher.DemultiplexingDispatchMessageFormatter.DeserializeRequest(Message message, Object[] parameters)
   at System.ServiceModel.Dispatcher.UriTemplateDispatchFormatter.DeserializeRequest(Message message, Object[] parameters)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.DeserializeInputs(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContext

the error doesn't come from the databse, you aren't processing the return value right, and send '' to be converted or processed. so where is that code exactly ? — nbk, Sep 23 '22 at 11:16
Please [edit] the question to include a [MRE] with: the code which you are using to construct and pass the payload to the third party server; and the documentation for the third-party API so that we know how a correct payload should be constructed. The error is not obvious that it has anything to do with Oracle. — MT0, Sep 23 '22 at 11:45
If the question is about how to convert a timestamp to an ISO8601 formatted string the this question is a duplicate of [Timezone date format in Oracle](https://stackoverflow.com/q/40841489/1509264) — MT0, Sep 23 '22 at 11:48

score 3 · Accepted Answer · answered Sep 23 '22 at 11:16

If you have to pass such a format, then format value returned by the SYSTIMESTAMP function.

For example:

SQL> select to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss:ff7TZR') val from dual;

VAL
-------------------------------------------------------------
2022-09-23T13:15:53:7027320+02:00

SQL>

convert system date to timestamp format

1 Answers1