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}".

conclusions:
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;
try
{
    t = mysql.ExecuteSelect(query).Tables[0];
}
catch (Exception ex)
{
   
if (ex.Message.Contains("Unable to convert MySQL date/time value to
                            System.DateTime"))
    {
         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;
         mysql.ExecuteUpdate(query);
         query =
"SELECT * FROM table WHERE ID = " + ID;
         t = mysql.ExecuteSelect(query).Tables[0];
    }
    else
       throw ex;
}





Comments

Popular posts from this blog

c# Service Play Sound with NAudio example by Moshe

JS/JQ simulate Enter event

SOLVED The item could not be indexed successfully because the item failed in the indexing subsystem