Monday, July 23, 2012

c# to MySql DateTime / TimeStamp Format

this is not about how to extract datetime formats from mysql, there r many examples AND if ur using C# / .NET then the VS knows the job and doesn't need ur help.

BUT! when i try to put MY datetime to the mysql  - nothing goes there!
well, that's cuz i'm not familiar with mysql datetime formats, and yes, i am not using SP, just simple queries.
1 - the format is yyyy-MM-dd hh:mm:ss, that is:
     string date = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
2 - TimeStamp: - on INSERT doesn't get value (null - reading-parsing) and wont accept 1970 and earlier
3 - and since any other mistake would NOT resolve in null but something unsolved by me, in an sql query u can only see them like that:
SELECT UpdateDate FROM franchise_information WHERE UpdateDate LIKE '% %';
for C# u'll need this:
MySqlDateTime d = r.GetMySqlDateTime(0);
anything else would give you EX : "Unable to convert MySQL date/time value to System.DateTime"
and that is cuz if u'll try d now u'll see "{0/0/0000 00:00:00}".

when inserting new stuff where there is a timestamp (on update):
1 - put a value
2 - don't call it
3 - call it safe

worst case scenario:
query = "SELECT * FROM table WHERE ID = " + ID;
    t = mysql.ExecuteSelect(query).Tables[0];
catch (Exception ex)
if (ex.Message.Contains("Unable to convert MySQL date/time value to
         string date = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                 //could be either of these
         query = "UPDATE table SET UpdateDate = '1971-01-01'
                  WHERE ID = " + ID;
         query =
"SELECT * FROM table WHERE ID = " + ID;
         t = mysql.ExecuteSelect(query).Tables[0];
       throw ex;

No comments:

Post a Comment