ORM and date queries[Solved]

larand54 · 2019-03-30 12:58:42

The following does not work as I expected:

  Baby := TSQLBaby.Create;
  Baby.name := 'Kurt';
  Baby.Sex := sMale;
  dt := encodeDate(2018,01,03);
  Baby.BirthDate := dt;
  Baby.Address := 'Lindesberg';
  aClient.Add(Baby, true);
  Baby.Free;
  
  Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate = ?',[DateToSQL(dt)]);
  while Baby.FillOne do
  begin
    Memo1.lines.Add(Format('>>>>>>>>>>  %s - %s ',[Baby.Name, Baby.Address])+' // '+FormatDateTime('yyyy-mm-dd', Baby.BirthDate));
  end;

BirthDate and dt is clearly the same date so why didn't I get a hit from the query?

Last edited by larand54 (2019-04-07 16:47:05)

edwinsn · 2019-03-31 09:06:14

Try DateTimeToSQL instead.

A related discussion is here: https://synopse.info/forum/viewtopic.php?id=4909

larand54 · 2019-04-05 06:44:22

It doesn't matter if I change to DateTimeToSQL. Still no hit.
You only get hits when using :

Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate <> ?',[DateTimeToSQL(dt)]);

or whenever you use '<' or '>' but thats not what I want in this case. I want hits on a certain date.

Maybe this wont work with fpc and lazarus? But I thought it would.
I'm running on Windows10.

Still hope on some help or explanation

AOG · 2019-04-05 07:06:02

You could try this:

Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate>? and BirthDate<?',[DateTimeToSQL(StartOfTheDay(dt)),DateTimeToSQL(EndOfTheDay(dt))]);

larand54 · 2019-04-05 10:40:59

I just tested that, but, sorry ... no hit.
Date seems to be a problem in mORMot.
Maybe it could also depend on SQLite3? I never had any such problems in Delphi with firedac and MSSQL.

AOG · 2019-04-05 12:40:10

If you use sqlitebrowser, what does the BirthDate column data show ?

https://sqlitebrowser.org/

larand54 · 2019-04-05 14:42:36

I use SQLIteStudio 3.2.1
which gives the following output:

ID	Name	Address	BirthDate	Sex
310	Fredrik	Stockholm	43259.5122240741	1
311	Kristina	Lund	43294.5122242593	0
312	Kurt	Lindesberg	43103	1

The last line is the line I searched for which is 2018-01-03.

AOG · 2019-04-05 16:16:51

There you go [replace > with >= and < with <= ... I hope this will work]:

Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate>=? and BirthDate<=?',[DateTimeToSQL(StartOfTheDay(dt)),DateTimeToSQL(EndOfTheDay(dt))]);

larand54 · 2019-04-05 17:50:34

Sorry, but that didn't work either.
Can there be any more solutions to test?

AOG · 2019-04-06 06:21:12

In my sqlite-databases, a datetime looks like:

2017-10-26T15:47:14

In your case, its just a float (double). So DateTimeToSQL will not work. Try:

Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate>=? and BirthDate<=?',[Double(StartOfTheDay(dt)),Double(EndOfTheDay(dt))]);

larand54 · 2019-04-06 12:02:59

Finally it worked

I could also reduce it to:

  Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate=?',[double(dt)]);

So you may ask... Why all this about using DateToSQL and DateTimeToSQL when you should cast it to a double?
It has to be some other situations when you need to use them but when?

Thank's !

PS
Is there any Convention how to mark a thread solved here ?
DS

edwinsn · 2019-04-07 03:43:41

larand54 wrote:

Finally it worked
I could also reduce it to:
  Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate=?',[double(dt)]);
So you may ask... Why all this about using DateToSQL and DateTimeToSQL when you should cast it to a double?
It has to be some other situations when you need to use them but when?
PS
Is there any Convention how to mark a thread solved here ?
DS

It's not very normal that the datetime values are stored as Double, what type is TSQLBaby.BirthDate? If it's defined as either TDateTime or TDate, and if the saving is done via mORMot, the field values stored to the db should be something like @AOG posted, in iso8601 format, by design of the framework, check the doc here.

I usually edit the forum post and add '[solved]' to the title.

Last edited by edwinsn (2019-04-07 03:44:13)

larand54 · 2019-04-07 16:46:00

Ok, that solved some mystery about this. I have declared BirthDate as TDate and that result as FLOAT in SQLite3. When I changed to TDateTime I got TEXT in SQLite3.
Strange that TDate and TDateTime is not treated the same here.

That solved everything about this and if I had choosen TDateTime from the begining, this topic would not been created

mORMot Open Source

#1 2019-03-30 12:58:42

ORM and date queries[Solved]

#2 2019-03-31 09:06:14

Re: ORM and date queries[Solved]

#3 2019-04-05 06:44:22

Re: ORM and date queries[Solved]

#4 2019-04-05 07:06:02

Re: ORM and date queries[Solved]

#5 2019-04-05 10:40:59

Re: ORM and date queries[Solved]

#6 2019-04-05 12:40:10

Re: ORM and date queries[Solved]

#7 2019-04-05 14:42:36

Re: ORM and date queries[Solved]

#8 2019-04-05 16:16:51

Re: ORM and date queries[Solved]

#9 2019-04-05 17:50:34

Re: ORM and date queries[Solved]

#10 2019-04-06 06:21:12

Re: ORM and date queries[Solved]

#11 2019-04-06 12:02:59

Re: ORM and date queries[Solved]

#12 2019-04-07 03:43:41

Re: ORM and date queries[Solved]

#13 2019-04-07 16:46:00

Re: ORM and date queries[Solved]

Board footer