CODEWITHSUNDEEP
muledataweavemulesofttimestamp-with-timezone
ooz
ooz

Reputation: 13

How to convert Timestamp with Time Zone to LocalDateTime in DataWeave

I'm trying to transfer an object from an Oracle database to a SQL Server database, using Mulesoft. One of the columns is a Timestamp with Time Zone but whenever I transfer it, I get this error:

org.mule.runtime.core.internal.message.ErrorBuilder$ErrorImplementation

description=Error converting data type nvarchar to datetimeoffset.

errorType=DB:QUERY_EXECUTION
cause=org.mule.extension.db.api.exception.connection.QueryExecutionException errorMessage=-
childErrors=[]

I tried using this solution but it's still not working:

 payload.FILE_DATE_INSERT as String {format: "DD-MON-RR HH.MI.SSXFF AM TZR"} as LocalDateTime {format: "yyyy-MM-dd'T'HH:mm:ssZ"}

Error:

Cannot coerce Object { class: oracle.sql.TIMESTAMPTZ } (org.mule.weave.v2.module.pojo.reader.JavaBeanObjectValue@eb085528) to String

11| FILE_DATE_INSERT: payload.FILE_DATE_INSERT as String {format: "DD-MON-RR HH.MI.SSXFF AM TZR"} as LocalDateTime {format: "yyyy-MM-dd'T'HH:mm:ssZ"},

Trace:

at main (line: 11, column: 19)" evaluating expression: "{ FILE_CODE: payload.FILE_CODE, FILECONTENTTYPE_CODE: payload.FILECONTENTTYPE_CODE, FILE_NAME: payload.FILE_NAME, FILE_SIZE: payload.FILE_SIZE, FILE_STATUS: payload.FILE_STATUS, USER_CODE: payload.USER_CODE, FILE_UPLOAD_CODE: payload.FILE_UPLOAD_CODE, FILE_UPLOAD_TEMP: payload.FILE_UPLOAD_TEMP, FILESTORAGE_CODE: payload.FILESTORAGE_CODE, FILE_DATE_INSERT: payload.FILE_DATE_INSERT as String {format: "DD-MON-RR HH.MI.SSXFF AM TZR"} as LocalDateTime {format: "yyyy-MM-dd'T'HH:mm:ssZ"}, }". Error type : MULE:EXPRESSION Element : DOC_TFILEMigrationFlow/processors/1/processors/0 @ DatabaseConnectorPOC:docLogic.xml:75 (Insert into DOC_TFILE) Element XML : {call InsertIntoFile (:FILE_CODE,:FILECONTENTTYPE_CODE,:FILE_NAME,:FILE_SIZE,:FILE_STATUS,:USER_CODE,:FILE_UPLOAD_CODE, :FILE_UPLOAD_TEMP,:FILESTORAGE_CODE, FILE_DATE_INSERT)} #[{ FILE_CODE : payload.FILE_CODE, FILECONTENTTYPE_CODE : payload.FILECONTENTTYPE_CODE, FILE_NAME : payload.FILE_NAME, FILE_SIZE : payload.FILE_SIZE, FILE_STATUS : payload.FILE_STATUS, USER_CODE : payload.USER_CODE, FILE_UPLOAD_CODE : payload.FILE_UPLOAD_CODE, FILE_UPLOAD_TEMP : payload.FILE_UPLOAD_TEMP, FILESTORAGE_CODE : payload.FILESTORAGE_CODE, FILE_DATE_INSERT : payload.FILE_DATE_INSERT as String {format: "DD-MON-RR HH.MI.SSXFF AM TZR"} as LocalDateTime {format: "yyyy-MM-dd'T'HH:mm:ssZ"}, }]

(set debug level logging or '-Dmule.verbose.exceptions=true' for everything)

Does anyone know how to properly convert it with Dataweave?

Thank you!

Upvotes: 0

Views: 3044

Answers (2)

Alex
Alex

Reputation: 4473

See, you are doing triple conversion String -> LocalDateTime -> whatever_it_is_in_the_database

The weak link is in the last leg of this conversion - what DW/metadata thinks about it and what it is really in the database.

May I suggest that inside Mule/DW you convert to string and then in the database you convert string to whatever format is required by DB?

At least you can control and see full understandable conversion in one place (actually in both - in DW and in DB).

https://simpleflatservice.com/mule4/AvoidCoversionsOrMakeThemNative.html

Upvotes: 0

maddestroyer7
maddestroyer7

Reputation: 263

Based off the example that you provided, you would need to adjust your formatting for your dw and then cast to a string to reformat. For example, 

%dw 2.0
output application/json
---
"10-NOV-19 02.06.50.708000000 PM EUROPE/LONDON" as DateTime {format: "dd-MMM-yy hh.mm.ss.SSSSSSSSS a VV"} as String {format: "yyyy-MM-dd'T'HH:mm:ssZ"}

will output

"2019-11-10T14:06:50+0000"

Upvotes: 1

Related Questions