format string in datetime c# to insert in MYSQL datetime column

Question

I have code like this:

AutoParkDataDataContext Db = new AutoParkDataDataContext();
Dailyreport dailyRep = new Dailyreport();
string time = Convert.ToDateTime("10-10-2014 15:00:00");
dailyRep.order_time = time;
Db.Dailyreports.InsertOnSubmit(dailyRep);
Db.SubmitChanges();

When I see it in the DailyReport table it shows me only the date ("10-10-2014 00:00:00:00"), so the time is ignored. How could i fix it? The column type is DateTime.

What data type is your DailyReport.order_time property? From the looks of your code it is a 'string' type. Maybe change the properties data type to DateTime? — maccettura
– maccettura, Commented Sep 9, 2013 at 19:23
As one answer (which maybe should have been a comment) notes, your code as it stands does not compile. Please give more precise code. Are time and order_time of type DateTime, or of type string? — Jeppe Stig Nielsen
– Jeppe Stig Nielsen, Commented Sep 9, 2013 at 19:57

radbyx · Accepted Answer · 2019-05-22 09:54:38Z

27

A quick/easy method to insert date or datetime into MySQL is to use the format 'yyyy-MM-dd', or datetime as 'yyyy-MM-dd H:mm:ss'.

Try this

DateTime theDate = DateTime.Now;
theDate.ToString("yyyy-MM-dd H:mm:ss");

Make your SQL look like this.

insert into mytable (date_time_field) value ('2013-09-09 03:44:00');

edited May 22, 2019 at 9:54

radbyx

9,72022 gold badges93 silver badges133 bronze badges

answered Sep 9, 2013 at 19:45

Derrick

2,0311 gold badge14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sohel Pathan Over a year ago

Yeah. It helped me. And saved lots of time.

Jaroslaw Stadnicki Over a year ago

Be sure to copy from code block not comment. I copied as 'yyyy-mm-dd hh:mm:ss' month was weird ;)

Mary Over a year ago

@jstadnicki That's because mm is minutes.

user2711965 · Accepted Answer · 2013-09-09 19:16:52Z

2

Your line:

string time = Convert.ToDateTime("10-10-2014 15:00:00");

Shouldn't compile.

I can only guess that you don't have DateTime as type of your column in SQL Server, you should modify it to keep DateTime and then pass a DateTime type object, not a string.

answered Sep 9, 2013 at 19:16

user2711965

1,8252 gold badges15 silver badges34 bronze badges

1 Comment

tnw Over a year ago

I just tried, it doesn't. Can't implicitly convert DateTime to string

Mike Perrenoud · Accepted Answer · 2013-09-09 19:22:19Z

1

This means that the underlying data type in the database must be Date. Change that to DateTime and it will store the time as well.

answered Sep 9, 2013 at 19:22

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

8 Comments

Mike Perrenoud Over a year ago

@maccettura, it's a DateTime .NET data type, unfortunately that has no bearing on the underlying data type in the database.

maccettura Over a year ago

His question clearly states "When I see it in the DailyReport table it shows me only the date ("10-10-2014 00:00:00:00"), so the time is ignored. How could i fix it? The column type is DateTime." MySql has a DateTime data type, does it not? Am I missing something from your comment?

Mike Perrenoud Over a year ago

@maccettura, if the OP is looking at the table and not seeing the time, then the OP is mistaking the data type of the column in .NET for the actual underlying data type. There is nothing wrong with the .NET code, so if the database was in fact a DateTime when looking at the table the OP would see the time.

maccettura Over a year ago

He is inputting a time of 15:00:00 and it is not showing up in the database (it shows as 00:00:00). There is still a time in the database, it is just a loss of precision. Meaning the problem lies in the code (again a loss of precision, most likely from the string>datetime conversions).

Mike Perrenoud Over a year ago

@maccettura, not lying. Mistaken. And yes, the InsertOnSubmit code isn't doing anything, it's a .NET framework method, they aren't dropping precision of data types for the fun of it.

|

Mark Rotteveel · Accepted Answer · 2018-05-19 17:46:08Z

1

DateTime dateTimeVariable = DateTime.Now;
string date = dateTimeVariable.ToString("yyyy-MM-dd H:mm:ss");

The insert Statement will look kind of like this:

string InsertQuery   = "INSERT INTO table( `fieldName` ) VALUES ( '" + date + "' )";

edited May 19, 2018 at 17:46

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jul 4, 2017 at 3:21

jeleel

213 bronze badges

3 Comments

jeleel Over a year ago

i don't understand why this got a down vote! Am i missing something ?

Mark Rotteveel Over a year ago

The main problem is that you posted a code-only answer without any explanation.

user6743474 Over a year ago

That is very harsh. The code really does not need much explanation - and it works.

Collectives™ on Stack Overflow

format string in datetime c# to insert in MYSQL datetime column

4 Answers 4

3 Comments

1 Comment

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related