SQL date problem

[limelect 48]

My record has a time date field. (This cannot change).

Looking with a database program I see 2021-10-03 10:09:23.411

On my database grid, I see 03-10-2021 10:09:23

All above is not important. I put it for clarity.

 

I have this 

      FDQuery2.SQL.Add('Select * from Projects');

      FDQuery2.SQL.Add('WHERE MyDateTime=:MyDate');<<<<??????
      FDQuery2.SQL.Add('ORDER BY MyDateTime');
      FDQuery2.Params.ParamByName('MyDate').AsDateTime := StrToDateTime('03/10/2021 10:09:23.411');
      FDQuery2.Open;

 

My PROBLEM

I want to filter only the date 03/10/2021. Although I have also the time in the record.

Any suggestion? 

Edited October 17, 2021 by limelect

[Josep 8]

Hello,

 

with SQL Server you can use: 

... WHERE cast(MyDateTime As Date) = :MyDate ...

to truncate the time of  MyDateTime field.

 

More info in: https://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server/923322

 

 

 

[limelect 48]

@Josep No, it did not work D10.2.3

      FDQuery2.SQL.Add('WHERE cast(MyDateTime As Date) =:MyDate');
        FDQuery2.SQL.Add('ORDER BY MyDateTime'); //=''03/10/2021''');
        FDQuery2.Params.ParamByName('MyDate').AsDate := StrToDate('03/10/2021');

I also tried ('MyDate').AsDateTime

Edited October 17, 2021 by limelect

[limelect 48]

@Josep Thanks it gave me a lead

 

It is       FDQuery2.SQL.Add('WHERE {fn CONVERT(MyDateTime,DATE)}=:MyDate');
 

Edited October 17, 2021 by limelect

[Stano 143]

Correct should be ... WHERE MyDateTime = CAST (: MyDate AS DATE) ...
Avoid using "StrToDate" functions. You will avoid the problem.
Use functions from the EncodeDate family
I was inattentive again.

Edited October 17, 2021 by Stano

[mvanrijnen 123]

FDQuery2.SQL.Add('Select * from Projects');
FDQuery2.SQL.Add('WHERE MyDateTime>=:MyDateFrom');
FDQuery2.SQL.Add('  AND MyDateTime<:MyDateTill');
FDQuery2.SQL.Add('ORDER BY MyDateTime');
FDQuery2.Params.ParamByName('MyDateFrom').AsDate  := StrToDate('03/10/2021'));
FDQuery2.Params.ParamByName('MyDateTill').AsDate  := IncDay(StrToDate('03/10/2021'));
FDQuery2.Open;

please keep forum in english.

Edited October 17, 2021 by mvanrijnen

[Fr0sT.Brutal 900]

21 hours ago, limelect said:

FDQuery2.Params.ParamByName('MyDate').AsDate := StrToDate('03/10/2021');

Good luck running on US machines

[mvanrijnen 123]

5 minutes ago, Fr0sT.Brutal said:

Good luck running on US machines

haha, the StrToDate shit, is just taken from the topicstart, ofcourse in realworld we do not use this in realcode. 

 

Edited October 18, 2021 by mvanrijnen

[limelect 48]

@mvanrijnen  @Fr0sT.BrutalWhat is your suggestion then?.

I know of the date problem month before day

The above date is just for the test.

However, in the real world DATE TIME will be stored in a database

and taken from there. Is there any problem? you think?

Edited October 18, 2021 by limelect

[mvanrijnen 123]

just for the example:

 

var

  myDate : TDate;
begin

    myDate := EncodeDate(2021, 10, 3);

....

// Make sure that myDate only contains the Date part, even when you declare a var as TDate it still can contain a time part !!

// so thats why i make sure in the assignment, it depends on you own code if you need the DateOf() here.

FDQuery2.Params.ParamByName('MyDateFrom').AsDate  := DateOf(myDate);
FDQuery2.Params.ParamByName('MyDateTill').AsDate  := IncDay(DateOf(myDate));

.....

end;

 

 

[Fr0sT.Brutal 900]

22 minutes ago, limelect said:

@mvanrijnen  @Fr0sT.BrutalWhat is your suggestion then?.

I know of the date problem month before day

The above date is just for the test.

However, in the real world DATE TIME will be stored in a database

and taken from there. Is there any problem? you think?

Well, your original question is " I want to filter only the date 03/10/2021. Although I have also the time in the record. " so the answer is one of:

- Find time-stripping function in your DB's SQL (let's name it DATEOF) and use 'WHERE DATEOF( MyDateTime ) = :Date' & Qry.Param['Date'] := DateOf(SomeDateTimeValue)

- Construct range checking SQL with Start-of-day-to-filter and start-of-next-day: 'WHERE MyDateTime >= :StartOfThisDay AND MyDateTime < :StartOfNextDay' & Qry.Param['StartOfThisDay'] := DateOf(SomeDateTimeValue) & Qry.Param['StartOfNextDay'] := IncDay(DateOf(SomeDateTimeValue))

Edited October 18, 2021 by Fr0sT.Brutal

[limelect 48]

@Fr0sT.Brutal Yes as my original question this answer it.

FDQuery2.SQL.Add('WHERE {fn CONVERT(MyDateTime,DATE)}=:MyDate');

Edited October 18, 2021 by limelect

[Serge_G 87]

On 10/17/2021 at 12:33 PM, limelect said:

 No, it did not work D10.2.3 

I disagree, it's entirely dependent on SGBD used

20 hours ago, mvanrijnen said:

the StrToDate shit,

Ok but you can use formatsettings

function StrToDate(const S: string; const AFormatSettings: TFormatSettings): TDateTime;

 

Edited October 19, 2021 by Serge_G

[Guest]

I do not work with the same RDBMS, but, to confuse this thread even more...

It is probably not noticeable but running the test value through a function will execute the function lots of times (if you do not have an expression index).

The alternate, to test the timestamp in a BETWEEN or in or:ed >= and < , as above, would probably be more efficient (but that needs to be measured), look at the plan and the count result of table accesses.

[limelect 48]

Well i will explain what I do and tel me that I am worng.

I take FILE date time and put it in a DB.

I guess every country has its format. (so I do not care)

Then I SQL (search DB grid) Date. Thats it.

why do I need this when I do not know where program will be used?

function StrToDate(const S: string; const AFormatSettings: TFormatSettings): TDateTime;

Edited October 19, 2021 by limelect

[mvanrijnen 123]

take a look at: System.SysUtils.FileDateToDateTime - RAD Studio API Documentation (embarcadero.com)

(assuming you get the filedatetime with a standard delphi function).

[limelect 48]

@mvanrijnen I am using FileInfo.TimeStamp where FileInfo: TSearchRec

and  >>> DateTimeToStr(FileInfo.TimeStamp));  >>> this to tree > to  DB

your knoledge ?

[mvanrijnen 123]

Why keep converting a Date(Time) to a string, keep it a a date and let FireDac worry about the conversion to the correct db format. 

(as said before, keep away from datetime to string conversion (or viceversa) with something like this)
 

What code you now have, the whole SQL statement and the assignment of parameter values?

 

Edited October 19, 2021 by mvanrijnen

[limelect 48]

Becouse the whole project is more complicated then just one line of code.Sorry.

That is a very simplified line of code

[mvanrijnen 123]

then use the function DateTimeToStr and StrToDateTime with the format options, already mentioned before. 

i would be sure then, to keep a fixed format in the database if it has to be a string like  YYYYMMNNHHNNSS, or use ISO8601 to store and retrieve. but keep the points of conversion as minimum as possible,

there will be a point where it's gonna bite you, been there, done that 🙂 

But at the end, it's something you have to figure out yourself. I would go back and take a look if you can change the

  >>> DateTimeToStr(FileInfo.TimeStamp));  >>> this to tree > to  DB

 

to FileInfo.TimeStamp----->Database

                                   ------>Visualize DateTimeToStr()

 

 

 

[limelect 48]

Thanks

[Fr0sT.Brutal 900]

From 10 of wise coder's commandments:

DO NOT store dates and floats as strings in DB. NEVER. If you do, you're wrong.

Sub-commandment:

If you have to serialize dates and floats, use formats carved in stone. F.ex., there are ISO formats for dates that allow sorting as strings to get exactly the same order as if were sorting as dates.

[limelect 48]

As i said it is more then meet the eye.

this is just part of the table

 FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS Projects (MyDateTime DateTime,ProjectName TEXT,ProjectrealName TEXT,Category TEXT NULL,FileMask TEXT NULL,Description BLOB,FilesIndex INTEGER)');

(MyDateTime DateTime <<<<<

Thanks in any case

[mvanrijnen 123]

Okay, so you DB is ok, it's a DateTime field (or timestamp field),  then the main problem is probably the storage in the tree, is this a visual treeview thing? 

best is to take a look at that, if you can improve the storage of the datetime value with the tree item, and keep a record or a pointer to a class instance with the correct data, so you can use your file datetime as a real datetime, then

you won't have the problems with the strtodatetime thing when quering the database.

 

it's only a tip, or let's say positive critisism 😉

 

 

Edited October 19, 2021 by mvanrijnen

[limelect 48]

Ok thanks