CODEWITHSUNDEEP
c#mysql.net
onik
onik

Reputation: 1631

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

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.

Upvotes: 10

Views: 56799

Answers (4)

Derrick
Derrick

Reputation: 2021

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');

Upvotes: 27

jeleel
jeleel

Reputation: 21

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 + "' )";

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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.

Upvotes: 1

user2711965
user2711965

Reputation: 1825

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.

Upvotes: 2

Related Questions