-1

I'm having a huge issue with inputting DateTime into my MySQL db. MySQL reads datetime as yyyy-MM-dd HH:mm:ss while C# sees datetime as dd/MM/yyyy HH:mm:ss. I have tried two different examples and both doesn't work.

MODEL

public class DateAndTime
{
    public DateTime DateTime { get; set; }
}

CONTROLLER

//I Have two examples that doesn't work.
 var myDateTime = "2016-04-08 12:00:00" //this gives the error "cannot covert source 'string' to tartget type System.DateTime

 var myDateTime = Convert.ToDateTime("2016-04-08 12:00:00") //this gives the error "Input string was not in a correct format."

var model =  new DateAndTime
{
   DateTime = myDateTime
};

So I'm stuck. I'ms sure someone out there has had this issue.

NeoSketo
  • 525
  • 1
  • 7
  • 26
  • 1
    How are you creating your SQL statement? If you use parameters then you shouldn't need to worry about formatting. – juharr Apr 08 '16 at 16:34
  • @juharr - the column DateTime in the table DateAndTime is in a MySQL db as yyyy-MM-dd HH:mm:ss. I use nHibernate to write to the db but im not getting that far yet with this error. – NeoSketo Apr 08 '16 at 16:52
  • You mean it's in the DB as a `varchar` instead of a `DateTime`? Because `DateTime` does not have a specific format, it's actually just a numeric value. The format used with SQL statements is just to make it so a human can understand it. – juharr Apr 08 '16 at 17:18
  • just double checked, db is datetime, class is DateTime, and var is DateTime. – NeoSketo Apr 08 '16 at 17:20
  • In that case there should be a way to do this and not worry about the formatting, but you really need to show the code that actually creates the query or the specific ORM you are using as it's not a matter of how to format the `DateTime` as a string, but how to make your ORM correctly work with your DB when dealing with `DateTime`. – juharr Apr 08 '16 at 17:23

3 Answers3

0
DateTime myDate = DateTime.ParseExact("2009-05-08 14:40:52,531", "yyyy-MM-dd HH:mm:ss,fff",
                                       System.Globalization.CultureInfo.InvariantCulture);

Check:

Converting a String to DateTime

https://msdn.microsoft.com/en-ca/library/cc165448.aspx

https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

Community
  • 1
  • 1
I. Charolia
  • 16
  • 2
  • using this: var myDateTime = "2016-04-08 14:18:17" and DateTime.ParseExact(myDateTime, "yyyy-MM-dd HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture) i get error "Input string was not in a correct format." Perhaps it's incorrect because its missing AM/PM? – NeoSketo Apr 08 '16 at 17:03
  • this is good. turns out i was converting it in the wrong format. THANKS I – NeoSketo Apr 08 '16 at 19:31
0

try this :

myDateTime.ToString("yyyy-MM-dd HH:mm:ss");

myDateTime variable must be type DateTime.

or you can use this MySql function : STR_TO_DATE

STR_TO_DATE('4/8/2016 2:18:17 PM', '%m/%d/%Y %h:%i:%s %p')

Specifier Format here

Abdellah OUMGHAR
  • 3,627
  • 1
  • 11
  • 16
0

I have this issue because of the datetime format in my machine. If your desktop is configured like dd/mm/yy(the / symbol instead of -) then you have to replace - with / before converting. Please let me know

Sivajith
  • 1,181
  • 5
  • 19
  • 38
  • My machine shows dd/mm/yyyy but it wouldnt matter because the end result has to write yyyy-MM-dd HH:mm:ss so im able to convert it. – NeoSketo Apr 08 '16 at 17:06