1

I am working on a desktop standalone application with SQLite.

In my application, I used to save the Datetime as DateTime.Now to identity when the user last visited, last logged in and many other purposes.. It works as expected for me.

Problem now is When I change the System Time format from "M/d/yyyy" to "d/M/yyyy" in the machine, Every thing messed up.

I got Exception in all the area where I used to retrieve the Datetime from Sqlite DB and convert that to datetime using Convert.ToDateTime("[retrived DB value]");

It seems like the the Convert.ToDateTime trying to convert the Datetime from different Culture to current culture.

I fell I implemented the design to save and retrieve DateTime in a wrong way. . Not sure.

What is the standard way of doing this, independent of system Datetime Culture? Any ideas?

Note: The field which I used in my DB to save the DatimeTime value is in "NTEXT" format.

Gopichandar
  • 2,742
  • 2
  • 24
  • 54
  • 3
    Is the field in the database a datetime or a string (nvarchar)? – user1793963 Oct 20 '15 at 08:39
  • I assume you save your `DateTime` values as a character. Looks like SQLite does not support to keep `DateTime` values as a binary. From 1.2 Date and Time Datatype part in this [link](https://www.sqlite.org/datatype3.html) ; saving it with `INTEGER` as Unix Time (seconds from 1970-01-01 00:00:00 UTC ) can be an option. – Soner Gönül Oct 20 '15 at 08:40
  • [The case against DateTime.Now](http://codeofmatt.com/2013/04/25/the-case-against-datetime-now/) – Soner Gönül Oct 20 '15 at 08:42
  • 1
    there are generally 3 way of recording a date, number of Ticks since a predefined date(this used to be the standard but different systems used different start dates making conversion a total pain), a floating point number in days where the fraction is fraction of a day (this is now the de-facto standard) and text which is just awful for everything except human readability, you should always convert to UTC before saving. – MikeT Oct 20 '15 at 08:59
  • as you are using NText you need to parse the date not convert it https://msdn.microsoft.com/en-us/library/kc8s65zs(v=vs.110).aspx – MikeT Oct 20 '15 at 09:27

2 Answers2

4

Just realized, in Sqlite, it stores Datetime as text/integer/real. I believe you have stored yours in format YYYY-MM-DD HH:MM:SS.SSS (TEXT as ISO8601). My evaluation based on this and only this

When retrieving you can parse exact:

var formatString = "YYYY-MM-DD HH:MM:SS.SSS";
var sample = "2010-06-11 22:19:12.123";
var dt = DateTime.ParseExact(sample, formatString, null);

Standard practice should be that storage to/from is in invariant culture. And when you want to display, this is where you convert from invariant to user's UI locale.

Also check this link which will help you solve the problem in short term. Which is basically specifying date time format in connection string.

Community
  • 1
  • 1
Yahya
  • 3,386
  • 3
  • 22
  • 40
  • That's not correct - SQLite stored dates as REAL, INTEGER or STRING with an ISO8601 format. This doesn't have anything to do with a UI locale or the Invariant culture – Panagiotis Kanavos Oct 20 '15 at 08:44
1

you are asking 2 questions here

1 is Now a reliable means of getting the current datetiem

DateTime.Now gets the current local time on the local machine, in many situations this is fine, however in any situation where you have multiple machines involved you should always use the UTC date which is UtcNow, as this stores the global time that can then be converted to the local time on which ever machine is using it.

Also if you are trying to have a common date to link events that happen at the same time you should always use the same source machine, usually the server to obtain the correct time as you don't want people being able to change the time on their machine and break the system

2 why does it break when i change the date format

this is because you aren't saving the date, you are saving a string and a string date is completely meaningless with out instructions on how to read(parse) it, this either has to be looked up automatically from your system (ie culture) or specified manually as you can never ensure that 2 machines are using the same culture then automatically looking it up generally is a very very bad idea.

I would suggest changing your database field to actually hold the date as a date not as text and converting to UTC before saving

MikeT
  • 5,398
  • 3
  • 27
  • 43