How do I display the following in a 'readable' datetime format?

Question

Database : SQLite  
Column : SomeTable.Logged (DateTime)  

Im using the System.Data.SQLite component. I save a datetime value in the Logged column as ticks. e.g. using c#

DateTime.Now.Ticks;

An example value that is saved in SomeTable.Logged is:

634399267463299880  

How, using sql, would I display this in a 'normal' date? e.g. '01-05-2011 13:45:22'?

I'm aware of the page http://www.sqlite.org/lang_datefunc.html , but I can't quite get things to work the way I want.

Answer 1

Try:

SELECT strftime('%Y-%m-%d %H:%M:%S',
                SomeTable.Logged/10000000 - 62135596800,
                'unixepoch')

where:

 62135596800 = DateTime(1970, 1, 1, 0, 0, 0).Ticks/10000000
             = number of seconds elapsed from 01/01/0001 00:00:00 
                                         until 01/01/1970 00:00:00;
 => SomeTable.Logged/10000000 - 62135596800
             = number of seconds elapsed from 01/01/1970 00:00:00
                                         until your date

 => 'unixepoch' to convert it to date value
 => '%Y-%m-%d %H:%M:%S' to format

example:

SELECT strftime('%Y-%m-%d %H:%M:%S',
                634398543220000000/10000000 - 62135596800,
                'unixepoch')

==> 2011-05-01 13:45:22