DATETIME datatype in mysql

Question

0.00/5 (No votes)

See more:

VS2015

Error:

JavaScript

Incorrect datetime value: '4/10/2016 5:50:48 AM' for column 'LastLogin' at row 1

Query:

C#

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:

JavaScript

Incorrect datetime value: '4/10/2016 5:50:48 AM' for column 'LastLogin' at row 1

Query:

C#

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.

Posted 9-Apr-16 2:57am

Kishor-KW

Updated 9-Apr-16 19:29pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-04-09T05:04:00

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!

Peter Leow · Answer 2 · 2016-04-09T04:59:00

Solution 1

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:

C#

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

Posted 9-Apr-16 4:59am

Peter Leow

James McCullough · Answer 3 · 2016-04-09T19:29:00

Solution 3

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! :)

Posted 9-Apr-16 19:29pm

James McCullough