I'm reading a timestamp from a mysql table using an OdbcDataReader. When I look at the data in the table it is in the format 2013-09-12 11:11:09. But the reader seems to read it in the format 12/09/2013 11:11:09.
I then try to insert this into another mysql table but receive the error:
Incorrect datetime value: '12/09/2013 11:11:09' for column 'timestamp' at row 1
How can I sort out this difference in formatting? Should I be referencing some Unix timestamp value somehow?
The data shouldn't be in the table in any text format. It's just a date and time.
You'll see the format when you convert the data to a string - which you should do as rarely as possible. In particular, when you're inserting the data into a different table, you shouldn't use a formatted value at all - you should use a DateTime in parameterized SQL.
Basically, unless you really need a string representation of the data, you should keep it in the "native" representation (DateTime in this case). Every time you have a conversion to or from text, that's an opportunity for failure. Dates and times are hard enough with time zones etc, without extraneous conversions getting involved.
reader contains the data read from the mysql table. cmd.CommandText = "insert into mytable set timestamp='" + reader["timestamp"] + "' where id=1"; This must be wrong - how should I be doing it?
INSERT INTO MyTable SET timestamp = @timestamp and then setting the @timestamp parameter. Look at the documentation for MySqlCommand.Parameters for examples.How are you looking at the data "in the table"? I'm not familiar with the MySQL implementation, but with Oracle and Sql Server datetime values are stored in an unreadable binary format, and translated to a readable timestamp by the query tool. MySQL is likely doing something similar.
try to insert this into another mysql table
If you care about format when you're inserting the data, you're doing something really bad. That's a strong indication you're using a technique that will be vulnerable to sql injection attacks, rather than parameterized queries. If you use parameterized queries, you assign a C# datetime type to the query parameter value directly, and the ADO.Net object handles any formatting you need. At that point, anything you can successfully DateTime.Parse() or DateTime.TryParse() becomes a valid input for your query.
