Database connector's Insert operation for inserting Datetime type issue

Question

I am using Mulesoft database connector insert operation, I need to insert currentTime into mssql column defined as datetime type.

When I use var currentTime = now() as DateTime {format: "yyyy-MM-dd HH:mm:ss"} it fails with error the conversion from unknown to timestamp is unsupported When I use var currentTime = now() as String { format: "yyyy-MM-dd'T'HH:mm:ss" }, it works. I thought now() as DateTime should work but not now() as String. could anyone give me some hints?

thank you in advance

Answer 1

Just to clarify why your solution works, DataWeave doesn't support SQL DateTime types so trying to use a DataWeave DateTime directly in an SQL operation usually fails. Depending of the specific database you are using it may auto convert a String with the right format into an SQL DateTime -which is what is happening in your solution- or the database may require some SQL expression to convert the String. For example in the case of Oracle using TO_DATE() or a similar function.