Date not insertable/updateable

Queries and Resources
,
1

Hello,

I have a table with three date fields. But none of the date fields are mandatory.
If I update the rows, because I changed certain values in certain rows, I am presented with a problem.
My MySQL Database has the dates columns defined as type DATE.
The tableColumns in the frontend are defined as type DATE.

When I am trying to save a changed row (where I did not change the date in its column), I am presented with the following error:

ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2022-09-16T00:00:00.000Z' for column 'since_date' at row 1

If I click in the datefield and just confirm the date, the row can get saved.

2

So, no one has an idea? Is this is bug in ReTool?
I am unable to update any row with dates, if I have not "changed" the date (e.g. by just clicking in it), because it is submitted as DateTime_withTimeZone, instead of Date. But changing the database entries form Date to DateTime brings no joy either.
Is this a ReTool MySql specific Bug?

3

Hey @ObiOne!

It looks like MySQL might be expecting your date to be formatted yyyy-MM-dd which is why changing the value causes it to be processed correctly:

You might try using a transformer along with moment to reformat your data before passing it to the update query or, you can do something similar as it's passed to the table itself.

Let me know if either of those work!

4

Formating the date with moment(DATE).format("yyyy-MM-dd") does work, but since I am using the bulk update feature and I am no pro in Javascript, how do I change the date-format on this:
{{ table1.recordUpdates.map(row => _.omit(row, ["status"])) }}
Is there someway to change the date-format, like I can omit the column status?

5

Yep! You can reassign the date property on the object after you've omitted "status" with something like

row => Object.assign(_.omit(row, ["status"]), {date: moment(row.date).format("YYYY-MM-DD")})

Does that work?

6

So far I can say, this did the trick. Thanks....!

7

And how can I do the same on a form? Omiting and re-formating?

8

It depends a bit on how you're referencing the form data:

  • If you're passing the full form.data you don't need to use map, you can try treating it as you would a single row since theObject.assign(...) expression works whenever you pass it an object that has the status and date properties.
  • If you're passing the fields individually to you're query you can use something like {{ moment(date1.value).format('YYYY-MM-DD') }}.
1 Like