Forum Cross Platform with Xamarin


The Xamarin Forums have officially moved to the new Microsoft Q&A experience. Microsoft Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

To create new threads and ask questions head over to Microsoft Q&A for .NET and get involved today.

Sqlite datetime convert to .Net datetime

Xamarin SQLiteConnection cant' convert datetime value. Here is my query :

var getDocumentsQuery = @"Select d.CreateDate From Docume Where d.IsSynced=0 ";
var documents = _db.Query<DocumentDTO> (getDocumentsQuery);

DocumentDTO class has property

public DateTime CreateDate { get; set; }
where I execute query direct to sqlite database it returns such value:

2015-10-21 04:20:30

but from xamarin , in my documents every DocumentDTO object has default datetime value:

documents[0].CreateDate {1/1/0001 12:00:00 AM} System.DateTime

So, SQLConnection unable to convert datetime value correct


  • YkshLeoYkshLeo USMember ✭✭✭✭


    Check this link, sometimes it can help you :smile:

  • MaitreFouMaitreFou REMember ✭✭

    Hi !
    In my case,
    when I started my project, datetime were saved in long int format in database (=ticks).
    I don't know why, but it changed one day (perhaps after an update of sqlite package?)
    I manually converted all datetime that was in wrong format in the db file to the format that is better for me (string : yyyy-MM-dd HH-mm-ss), and I forced the format in the declaration :
    db =new SQLiteConnection (dbPath, false); // false is for storeDateTimeAsTicks, default is true if not specified.
    Now dates are correctly saved, and correctly returned to c# code.
    I Hope it will help.

  • WVDRWVDR ZAMember ✭✭

    @tohwei Thank very much! This has been driving me crazy since if you create the table in a cross-platform app via Sqlite and set the property type as datetime(C#), the table column gets created as a bigint... This is actually great for storage size and speed. But If you don't know how to converted it to be read by a human; will leave you scratching your head. datetime(bigintdatecolumn/10000000 - 62135596800, 'unixepoch')works for me.

  • The constructor of SQLConnection takes an argument of storeDateTimeAsTicks which is by default is true make it false

  • GaetFGaetF Member

    @DineshPhalwadiya It only works if you do not have any constraints like working with an existing DB/table or manage backward compatibility without recreating the table

  • salmanpaulsalmanpaul Member ✭✭

    i write this command but still get the same result. select datetime(lastModified/10000000 - 62135596800, 'unixepoch'), * from TestTable

Sign In or Register to comment.