Error:Incorrect datetime value: '4/10/2016 5:50:48 AM' for column 'LastLogin' at row 1
Query:
string updateLastLogin = "Update mydb.user SET LastLogin='" + System.DateTime.Now + "' where Id='" + Session["UserId"].ToString() + "';";
is it that 4/10/2016 5:50:48 AM this value is not accepted by DATETIME datatype in mysql?
if yes then what should I do to store this value.
please help.
What I have tried:
Error:
Incorrect datetime value: '4/10/2016 5:50:48 AM' for column 'LastLogin' at row 1
Query:
string updateLastLogin = "Update mydb.user SET LastLogin='" + System.DateTime.Now + "' where Id='" + Session["UserId"].ToString() + "';";
is it that 4/10/2016 5:50:48 AM this value is not accepted by DATETIME datatype in mysql?
if yes then what should I do to store this value.
please help.
解决方案Simple: don't do that!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Passing the DateTime value as a DateTime in your C# code, (and the ID as whatever it should be, probably integer) will get rid of the problem, and will help to preserve your database - as well as making your code more readable.
Then go through the rest of your code and make sure you do the same everywhere else as well!
You need to convert it to mysql datetime format MySQL :: MySQL 5.7 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types[^]
try this:
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
To expand on Griff's answer (if I may be so bold) I do a lot of international data integration work. Please, for the sanity of everyone, NEVER USE STRINGS FOR DATES.
Half my mortgage has been paid by that, alone.
Wait... What am I saying? Keep me in business! :)