How to insert date-time into SQL

0
I am trying to insert the value I got in terms of date-time in a microflow but I keep getting this error. How do I fix this?
asked
2 answers
1

Hi Lifu,

you need to ensure that you're properly formatting the date value before inserting it into the SQL query. Here's the corrected code assuming you're using Mendix's SQL connector and want to insert a date value into the tbl_room_booking table:

"INSERT INTO tbl_room_booking (Date) VALUES ('" + formatDateTime($booking/Date, 'yyyy-MM-dd HH:mm:ss') + "')"

 

formatDateTime($booking/Date, 'yyyy-MM-dd HH:mm:ss') is used to format the date value as a string in the format 'yyyy-MM-dd HH:mm:ss', which is a common format for SQL databases.

answered
1

Hi Lefu,

The variable $book/Date is of type DateTime and not of type String.

Since the ‘SQL’ parameter is of type String you need to make sure that all the arguments you are giving or concatenating are converted correctly to type String.

You can use the function formatDateTime() to convert it to a String! Example:

formatDateTime($book/Date, 'YYYY-MM-DD HH:MI:SS')

You can read up on the function here: https://docs.mendix.com/refguide9/parse-and-format-date-function-calls/. I recommend reading it thoroughly since you can easily make a mistake (example using UTC when not needed or vise versa) which causes different date & times to be injected in to the SQL server.

answered