1

I want to update the datetime of a column but this is what the data is being inserted 01/01/0001 00:00:00. I can get the current datetime but when insert insert that to my sqlite database the result is 01/01/0001 00:00:00. How can I fix that?

var current_datetime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:00");
var current = Convert.ToDateTime(current_datetime);

string retailer_group_sql = "UPDATE tblRetailerGroup SET "'LastSync = '" + current + "', MobileUpdate = '" + current + "' WHERE RetailerCode = '" + retailerCode + "'";
await conn.ExecuteAsync(retailer_group_sql);
  • What column type are you using for LastSync/MobileUpdate? And what is the reason to convert a DateTime to a string, `current_datetime`, and then convert it back? – SushiHangover Nov 04 '18 at 15:41
  • @SushiHangover the column type is DateTime, The reason why i convert datetime to string is I need a certain datetime format. I converted it back because I think the datatype should be same as the column type right? or am I wrong? –  Nov 04 '18 at 15:48
  • A DateTime is a DateTime type, period, it does not change the internal storage structure due to what you are using to "Convert.ToDateTime" from. – SushiHangover Nov 04 '18 at 15:51

1 Answers1

2

Avoid using Convert.ToDateTime as that is slow and causes issues. Mainly, it won't throw an exception nor tell you if the input string is valid and will convert successfully (hence why you see that weird date). Your best option is to use DateTime.TryParse or DateTime.ParseExact. See Converting Strings to DateTime Objects from Microsoft Docs.

Returning to the question at hand, you may benefit from storing your DateTime objects as Int32 objects in your SQLite database. You can convert your DateTime to a Unix timestamp and store that value, instead of a string. This should be easier on the memory, and you won't have the trouble of converting the DateTime to String and back. Plus, it is culture independent.

Tom
  • 1,739
  • 15
  • 24