2

Im using AzureMobileServices for sync data but using SQLite for retrieving data because I could not get anything working for executing raw queries in azure services.

Im able to get records but my problem is with DateTime column in table. I always get weird date like 01/01/0001 or something like this.

Things I have tried: 

Parse<Model>().Query("select datetime(CreatedAt, 'unixepoch') CreatedAt from table"); 

Parse<Model>().Query("select strftime('%d/%m/%Y',CreatedAt / 10000000 - 62135596800,'unixepoch')   as createdAt from table");

Only time I get correct date and time when I run execute scalar and parse it into string like this:

connection.ExecuteScalar<string>("select datetime(CreatedAt, 'unixepoch') as createdAt from table limit 1");

Its not being parsed into my Model properly which has this property

public DateTime? CreatedAt {get; set;}

Edit

This is how it looks in sqlite

Schema of Datetime column Any suggestion?

Awais Shaikh
  • 531
  • 4
  • 15
  • You maybe have to parse to DateTime and convert to string with propery format for your model. How is the date stored? In which format? – Andreas Schmidt Oct 07 '19 at 17:57
  • I haven't worked with AzureMobileServices before, and after some searching it may be because of your null `?` property. Try changing it to `Nullable` instead. See this post: https://stackoverflow.com/questions/16692647/why-when-i-insert-a-datetime-null-i-have-0001-01-01-in-sql-server/16692795#16692795 – Rob Scott Oct 07 '19 at 19:26
  • the thing i do not understand is that this is working: `ExecuteScalar("select strftime('%m/%d/%Y %H:%M:%S',datetime(CreatedAt,'unixepoch')) from tblRegister limit 1")` whereas ExecuteScalar(same query) does not work – Awais Shaikh Oct 07 '19 at 19:54
  • @RobScott i tried Nullable but the issue remains same. – Awais Shaikh Oct 07 '19 at 19:55
  • 1
    @AwaisShaikh well in that case, your `Parse().Query(....` has `%d/%m/%Y`, instead of `%m/%d/%Y`, as you commented above – Rob Scott Oct 07 '19 at 20:59
  • @AwaisShaikh see https://stackoverflow.com/questions/41516093/cannot-read-nullable-datetime-value-from-sqlite-db-using-net-core-and-entity-fr perhaps? – Rob Scott Oct 07 '19 at 21:02
  • @RobScott i tried all formats but nothing gave me correct datetime. All primitive datatypes return correct data in `ExecuteScalar` method, string gives me `10/02/2019 15:55:00` , double gives me `10`.. – Awais Shaikh Oct 07 '19 at 22:58
  • The value returned is definitely relating to a `DBNull.Value` and the property the model is assigning too. Please post your table's column's datatypes and your whole model – Rob Scott Oct 07 '19 at 23:32
  • @RobScott I have edited my question with schema of the column – Awais Shaikh Oct 08 '19 at 08:45

1 Answers1

0

DateTime default value is 01/01/0001.Check the methods you use to create the model. The CreatedAt property throws a default value because of an incompatibility.

Emin YILMAZ
  • 30
  • 6