Best datetime implementation for syncing

[needledrag]

Hi all,

Just wondering what the recommended implementation for storing dates is when it comes to syncing dates (datetime) between Sqlite and SqlSever?

My SqlSever fields are currently stores as datetime, the Sqlite client dB shows the field as datetime but the values seem to be stored as ticks, which is fine but it means I need a converter to upload data from client to server and vice versa. That's also fine, but given the chance to change the database schema and start from scratch is there a 'best fit' for storing these fields in terms of performance and doing away with the converters which add a tiny bit of overhead?

Any suggestions or ideas appreciated

Shaun

[Mimetis]

If you are using SqliteParameter object when specifying your queries (when targeting SQLite) you should not have to convert types.
Just using DbType.DateTime is fine.

I'm using DateTime on Sql Server and the generated Sqlite database is fine. I never had to convert ticks for my dates in SQLite.

Just to have more details, can you share a sample where you need to convert your values ?

[needledrag]

I've done a little more testing. If I insert a datetime into a Sqlite table on the client and query it the date reads out fine into a datetime variable in the model(c#).

await _db.InsertAsync(myTableModel);

When having the data come from SqlServer in a sync all the dates queried are read as 01-01-2001 into the datetime variable.
I'm not using a SqliteParameter object in the query, my query is something like this :

var recalls = await _db.Table().Where
(x => x.Active == true).OrderByDescending(x => x.DtOfAlert).Take(numRecs).ToListAsync();

So if I have inserted the date on the client side using _db.InsertAsync(myTableModel) and query the date as above into a model with the variable as DateTime it reads fine with no conversion needed.

If the date is coming from a sync from SqlServer (where the field is also datetime) it appears in the Sqlite table as the correct date but reads as 01-01-2001. Does this mean I should alter all my queries on the tables that were synchronised to use SqlParameter objects?

At the moment I get around it by altering my model and reading the synced date field into a string variable and parsing this into a datetime variable in the model :
public string DtOfAlert { get; set; }
public DateTime DateOfAlert => DateTime.Parse(DtOfAlert);

So ultimately it's not a massive problem but I was wondering if you have any insights into why the dates synced read as 01-01-2001 and require the conversion? Is this down to the way I'm querying this data?

I should also mention that the dates read from SqlServer weren't dates that were uploaded by the client but a fresh set of initial records that all clients start with.

Regards

[Mimetis]

You should NEVER use a string conversion with dates.
NEVER EVER

That being said, you dates seems legit (01-01-2001 in SQL Server; and 01-01-2001 in SQLITE)
What's the problem ?

[needledrag]

Yeah it was kind of a last resort or easy way out I guess. Sorry it's not 01-01-2001, it reads them all as 01-01-0001 12:00:00 which as far as I know is DateTime.MinValue or a null date?
The problem is the date in SqlServer and in the Sqlite table is 2021-03-10 16:40:03.14 but when any of the dates are read they are all read into the model as 01-01-0001. This doesn't occur when I've inserted the dates on the client, only when they've come down in a sync?

[Mimetis]

I don't know.
If you can create a small reproduction sample, using just one table with the datetime, and the code you are using, I will be able to help you
Otherwise, it will be complicated

[needledrag]

Ok so after looking into it I've realised the app is using the Sqlite-net-pcl library to initally create the sqlite db and open the Sqlite connection which has a default parameter flag of storeDateTimeAsTicks = true. (while the tables were created and data synced using Microsoft.Data.Sqlite by Dotmim.Sync) This explains the difference in date formats when dates are inserted on the client (as ticks) and how they are inserted in the Sync process.

Is there a way to store the synced dates as ticks ie a flag in the sync process process? or I could probably just use a converter on the client side to change the dates to ticks right?