C# query datetime off sqlite database fails

Question

I am reading a sqlite file in which datetime column data are saved as integer values (INTEGER NO NULL)

DateTime dt=reader.GetDateTime(nColDateTime);

But it emits an error saying that the return value is not in correct format. I try out all other available methods in Datetime class and find only

DateTime dt=DateTime.FromBinary(reader.GetInt64(nColDateTime));

works (as others return exceptions). But the formatted date (as dt.ToShortDateTime()) is incorrect (ie 0042/11/20) I have no idea what this is. I then try this

long d=DateTime.Now.Ticks-reader.GetInt64(nColDateTime);
DateTime dt=new DateTime(d);

It gives me 1970/05/18

Could you help me to get the correct datetime ?

Answer 1

Your dates are stored in the Unix epoch format.

You probably just want to use:

private static readonly DateTime epoch = new DateTime(1970, 1, 1);

...

var myDate = epoch + TimeSpan.FromTicks(reader.GetInt64(nColDateTime));

---

For example, when I look at your example above "1970/05/18", I can assume that your date is approximately 5 months, 18 days earlier than today.

Here is how I would retreieve the original value:

(DateTime.Today - new DateTime(1970, 5, 18)).Ticks

Which returns:

13119840000000000

Plugging that into my formula:

new DateTime(1970, 1, 1) + TimeSpan.FromTicks(13119840000000000)

This returns:

2011/07/30