How to save datetime in mysql? Please help

Ask Community
#1

We have a sale form with date picker that output unix timestamp.
We convert to Date String like this

DATESTR( documentdate , "YYYY-MM-DD")

Therefore, we get e.g 2021-10-14
In Mysql Query Builder, we test with sample date string parameter, query is able to insert data in mysql database.

But in mysql connector, it is not working to insert sale data.
Even date string parameter is there, it keep saying

Incorrect datetime value: '' for column 'documentDate' at row 1

image
image1167×357 32.1 KB

Please guide us how can we save datetime in mysql

#2

@ygnthetzaw

What type of date value is your DB expecting?
Also, can you check and share your query which you have created in query builder to add this value?
Also, can you share screenshot of how you have used this query inn your app for better understanding?

#3

Hi @fenil.jain,

The Data Type is " DateTime " as documentDate in Table.
This is Insert Query in Mysql Query Builder

INSERT INTO `dealersaletransaction`
(
`employeename`,
`employeenumber`,
`zoneid`,
`documentType`,
`documentDate`,
`shopcode`,
`shopname`,
`shopphonenumber`,
`shopaddress`,
`busstopname`,
`remark`,
`imagefile`,
`status`,
`vouchernumber`,
`products`,
`InitiatorPosition`,
`created_by`,
`created_at`
)
VALUES
('{{employeename}}',
'{{employeenumber}}',
'{{zoneid}}',
'{{documentType}}',
'{{documentDate}}',
'{{shopcode}}',
'{{shopname}}',
'{{shopphonenumber}}',
'{{shopaddress}}',
'{{busstopname}}',
'{{remark}}',
'{{imagefile}}',
'{{status}}',
'{{vouchernumber}}',
'{{products}}',
'{{InitiatorPosition}}',
'Dealer Manager',
 now()
);

This is success query I test in mysql query builder.

image
image1611×786 55.5 KB

But in mysql connector, I test run in refresh connector response.
I have this issue.

image
image1917×983 122 KB

Please let me know if need more info.

#4

@ygnthetzaw
Can you clear cache and hard reload once and try running and saving your query again in mysql query builder once and then try again in your app?

Do let me know if this works for you or not?

#5

Yes @fenil.jain. After I do as you say, it’s working.

  1. I put datetime as NOT NULL in my db table.
  2. Clear Cache with CCleaner
  3. Reconnect query again
  4. Refresh connector response again.
    and then it’s working.

Thank you for your help!