Jump to content
limelect

SQL date problem

Recommended Posts

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 by limelect

Share this post


Link to post

@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 by limelect

Share this post


Link to post

@Josep Thanks it gave me a lead

 

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

Edited by limelect

Share this post


Link to post

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 by Stano

Share this post


Link to post
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 by mvanrijnen
  • Like 1

Share this post


Link to post
21 hours ago, limelect said:

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

Good luck running on US machines

  • Like 1

Share this post


Link to post
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 by mvanrijnen

Share this post


Link to post

@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 by limelect

Share this post


Link to post

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;

 

 

Share this post


Link to post
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 by Fr0sT.Brutal

Share this post


Link to post

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

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

Edited by limelect

Share this post


Link to post
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 by Serge_G

Share this post


Link to post
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.

Share this post


Link to post

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 by limelect

Share this post


Link to post

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

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

your knoledge ?

Share this post


Link to post

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 by mvanrijnen

Share this post


Link to post

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

That is a very simplified line of code

Share this post


Link to post

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()

 

 

 

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post

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 by mvanrijnen

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×