Can't Update Database Incorrect datetime value

App Building
1

I am using Date components in my form. When I submit the form to MySQL I am getting the error

Incorrect datetime value: '2023-10-01T00:00:00.000Z' for column 'Estimating_TargetDate' at row 1

If I change the date in my form or just click in the date field, the update query is successful. If I do not change the date in my form or I do not click in the date field, the update query fails.

I am just trying to insert the date as 08/30/2019 so I using the format MM/dd/yyyy.

Please help.

2

Hi @yourbudweiser, what is the datatype of column 'Estimating_TargetDate'?
Seems to be DATETIME()?

Mysql expects dates to be formatted as '2019-09-30', so in order to insert that date you have to format it properly.

With MomentJS, you could do
moment(08/30/2019).format(‘YYYY-MM-DD’)

3

Hi Ryan, thanks for your reply.

I updated the date field format to {{ moment(date_contractNoticeToProceedDate.value).format('YYYY-MM-DD') }}

date-format

but I am still unable to update the records:

"update projects set Contract_NoticetoProceedDate = '2023-10-31T00:00:00.000Z' where ID = 8 - Incorrect date value: '2023-10-31T00:00:00.000Z' for column 'Contract_NoticetoProceedDate' at row 1"

4

You’ll also need to change the formatting used in your query, now you only have changed the formatting for the date field. The value is still being inserted as datetime in you mysql db.

5

I'm using the GUI mode to update an existing record and the form object,

{{ contractSetupForm.data }}

Where/how do I change the generated query?

query
query1026×462 36.5 KB

6

A couple of options:

  • gui mode using the key value pairs and assign the values to the db fields yourself, you can then apply the formatting
  • change to sql mode and write the update query yourself
  • create JS transformer to modify the data from the form and use that output to update the db in your query
    -change the date_contractNoticeToProceedDate.value field to be a date and time field and use fictional time , eg. 1 am
  • change the database field to be date only

If you don’t care about the time, then the latter is probably the best and quickest option.

7

Thanks @mbruijnpff,

The database field is already set to date

date
date771×31 4.79 KB

and just clicking in the input field, without even modifying the existing value allows the form to be submitted. Watching in the browser inspector, I can see that the value does not change by clicking in the field.

I can use the key value pairs if need be but the form has 27 fields and that is not counting the other forms that use date fields as well. I am new to Retool and this seems to be such a complicated process just to save a date.

There must be some other way?

8

Try to update a single record, only that field, using the sql mode. Make sure you format the date correctly and see if that is working.

9

Yes, this update statement works using SQL mode:

UPDATE projects SET Contract_NoticetoProceedDate = {{ moment(date_contractNoticeToProceedDate.value).format('YYYY-MM-DD') }} where ID = {{ projectTable.selectedRow.ID }}

BUT each time I submit the form, one day is subtracted from the date.

sql-mode
sql-mode1344×485 45.1 KB

10

If you hover over the date in the sql query, does it show the proper date?
Where are you located? Might be an issue with timezones as Retool servers are located in US West.

11

It shows

moment(date_contractNoticeToProceedDate.value).format('YYYY-MM-DD')
Inspect

"2023-10-15"

date
date949×281 50.3 KB

I am located in US East.

12

Any help to resolve is really appreciated.

13

As it stands, without any further input, I'll have to use SQL mode and write the query but the issue remains that each time I submit the form, one day is subtracted from the date.

UPDATE projects SET
Contract_NoticetoProceedDate = {{ moment(date_contractNoticeToProceedDate.value).format('YYYY-MM-DD') }}
where ID = {{ projectTable.selectedRow.ID }}

date-format
date-format327×635 26.8 KB

14

I was able to resolve the date issue by using the 'moment.utc' method.

{{ moment.utc(date_proceedDate.value).format('YYYY-MM-DD') }}