I have to query my table for datetime interval. Suppose I have following 4 rows:
id typeId start end
1 11 18.09.2014 18.09.2014 23:45:00
2 11 19.09.2014 19.09.2014 23:45:00
3 12 18.09.2014 18.09.2014 23:45:00
4 12 19.09.2014 19.09.2014 23:45:00
Linq query is following:
model.Entities.Where(t => t.StartTime >= start && t.EndTime <= end);
which becomes something like that:
a.[StartTime] >= strftime(
a.[EndTime] <= strftime(
p2 = '2014-09-18' and p3 =2014-09-19 23:45:00.000
As a result I get 2 rows instead of 4 rows. I've invastigated problem and saw that if format string had %S instead of %f than everything works great. I don't know wheather it is a bug or expected behaviour, but I would like to get correct results.
I guess that System.Data.Sqlite provider is responsible for generated sql code, but reading source code of this provider ( SqlGenerator.cs and SQLite3.cs) I didn't found anything looking strange to me.
The question is: can I influence somehow on generated sql code, can I provide a hint to use '%Y-%m-%d %H:%M:%S' format string instead of '%Y-%m-%d %H:%M:%f' ?
I understand that it is possibly not OA problem, but I'm working with OA directly so maybe you will give some hints or direction to look for solution.
Sqlite provider version is 220.127.116.11, OA version is 2015.1.225.1
Thanks in advance.