SQL - looks easy but I cannot solve it

[Nicole]

But I also prefer the YYYY-MM-DD notation

How would you generate a string as best stlye for SQL use if there is given a date?

In other works.
myDateTimePicker.Date => myDateString

usage idea
select * from myTable where mydate = MyDateString

(who did not follow the thread: ParamByName is not possible)

[af0815]

I never see in the whole thread that a param is not useable for a date.

To generate a string for a date is ever to open a can of worms on a sql. Dealing with UTC, local time and summertime is hard enough.

[rvk]

But I also prefer the YYYY-MM-DD notation

How would you generate a string as best stlye for SQL use if there is given a date?

In other works.
myDateTimePicker.Date => myDateString

usage idea
select * from myTable where mydate = MyDateString

(who did not follow the thread: ParamByName is not possible)

You can use FormatDateTime('yyyy-mm-dd', now) to convert a date to a string.

But why would you use a string in your SQL?
Why can't you use Parameters?
You should always parameters if you're dealing with userinput !!!

[egsuh]

I’m really confused.  Based on the initial post I think you should keep y.id_trade <= x.id_trade in the sum(ergebnis) clause even though you take out date related condition, if the same sum is problem.

[Thaddy]

What is so difficult? This has nothing to do with any format. The order is always year/month/day/time. And there is a good reason for that: TDateTime. It also should be always UTC.

[Zvoni]

It also should be always UTC.

Have to disagree with the „always“.
It depends on the usecase.
E.g. i work for a company with branches the world over, and e.g. all dates/times are local to the branch, where a transaction occurred.
That way you can see from the raw data, when has who done what.
It was an intentional decision of people with a way higher paygrade than me.

[af0815]

It was an intentional decision of people with a way higher paygrade than me.

+1

In Austria we say "Ober sticht Unter" it means like the same. BTW. I think this can't translated by google (Viennese to english):-)

[Nicole]

Nicole comes from Austria as well.

Why I think it not possible to do by ParamByName:
The date-string of the SQL is generated by a different unit.

[rvk]

Nicole comes from Austria as well.

Why I think it not possible to do by ParamByName:
The date-string of the SQL is generated by a different unit.

I'm not sure if you know... but you can just do .ParamByName('DATEPARAM').asDateTime.
(And if you need... you can convert the date string before you put it into .asDateTime.)

It's ALWAYS best not to use literal strings for dates but asDateTime when possible.
You can also use .asData and .asTime.

You could put a date string directly inside the SQL but if the string comes from a user input then this is a BIG NO NO.
Then you have a HUGE security risk where the user can delete the complete database.

So... it's ALWAYS best to use parametrized queries. PERIOD.