What exactly is the datatype input into a DATETIME value in MySQL

All we need is an easy explanation of the problem, so here it is.

I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right format.

Actual question: I assume it’s string, because when I manually input values using and INSERT, the value has to be in ‘ ‘. Is this a stupid assumption on my part?

When the output of my PLC is a string and the datatype of my column is DATETIME, what I read from a SELECT in my DB is completely wrong : ‘1905-07-13 00:00:00’ instead of ‘2021-04-30 08:51:30’. However, if I write it in the DB column set to be of type VARCHAR, it’s written correctly, but not usable for my next app. So if I then change the column to DATETIME, everything works great, but that’s super impractical and I can be changing the datatype of my column every time I need to add to the DB.

Another assumption I’m making here is that the input datatype is what’s causing this problem, but as it is now, DATE and DATETIME are not supported by the driver I’m using, so maybe it’s just wishful thinking on my part that any of this will work at all.

Note: Please do migrate this if it’s off topic, wasn’t really sure if it was a best fit here or stack overflow…

Second Note: The only way I have found to make this work is to write into the DB as a VARCHAR and then use CONVERT(,DATETIME) in my app, but it feels weird that I can’t just write a date in my DB for some reason…..

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You must provide the value as string literal or as a numeric literal.

Possible formats are described in MySQL 8.0 Reference Manual / … / Date and Time Literals.

Alternatively you may provide the data in any custom format and apply STR_TO_DATE() to it in INSERT query with according parsing pattern.

DEMO

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply