CODEWITHSUNDEEP
c#asp.netmysqlsqlc#-4.0
user1387147
user1387147

Reputation: 955

Date time format issue in c#

I am working in asp.net using c# getting this error 1292 invalid date format when writing sql simple insert query but it goes fine with entity frame work why it is giving error with sql insert query? for database using mysql and column format is datetime format.

SQL code

INSERT INTO `log`(`LogID`,`Time`,`Details`,`UserName`,`LogType`)
VALUES('0','6/30/2012 3:47:22 PM','testaaaa Deleted','admin','1');

For entity framework

log.AddLog();
//Where log is table object name.

Upvotes: 0

Views: 981

Answers (4)

O. Jones
O. Jones

Reputation: 108641

You want to use STR_TO_DATE to get MySQL to interpret your nonstandard date/time string correctly when you use SQL directly.

STR_TO_DATE('6/30/2012 3:47:22 PM', '%m/%e/%Y %h:%i:%s %p')

should do the trick for you. Accordingly, your insert statement becomes:

INSERT INTO `log`
  (`LogID`,
   `Time`,
   `Details`,`UserName`,`LogType`)
VALUES
  ('0',
   STR_TO_DATE('6/30/2012 3:47:22 PM', '%m/%e/%Y %h:%i:%s %p'),
  'testaaaa Deleted','admin','1')

The MySQL entity framework code knows how to convert Dotnet dates to the appropriate format for MySQL code. That's why it works correctly.

The fiddly little %m %h stuff is shown here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Upvotes: 3

Sarma Pulletikurti
Sarma Pulletikurti

Reputation: 21

I think......pls check table name..it should not be quoted in single.....only values should be in single quotes.......

Upvotes: -1

Darcon
Darcon

Reputation: 219

There are different formats accepted for the datetime field. I'm pretty sure you can't use PM in the format you are using. You can only use 4 PM, 3 PM etc. Try removing the PM part and see if that works for you.

Upvotes: -1

peroija
peroija

Reputation: 1982

pretty sure you have to enter in this format: YYYY-MM-DD HH:mm:SS

so try

INSERT INTO `log`(`LogID`,`Time`,`Details`,`UserName`,`LogType`)
VALUES('0','2012-06-30 15:47:22','testaaaa Deleted','admin','1');

Upvotes: 2

Related Questions