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

Posts

  • YkshLeoYkshLeo USMember ✭✭✭✭

    @Nino,

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

    http://www.techonthenet.com/sqlite/functions/datetime.php

  • 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

Sign In or Register to comment.