Recent

Author Topic: SQL - looks easy but I cannot solve it  (Read 1968 times)

Nicole

  • Hero Member
  • *****
  • Posts: 745
Re: SQL - looks easy but I cannot solve it
« Reply #15 on: March 31, 2023, 08:36:29 pm »
Quote
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

  • Hero Member
  • *****
  • Posts: 1205
Re: SQL - looks easy but I cannot solve it
« Reply #16 on: March 31, 2023, 08:57:11 pm »
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.
regards
Andreas

rvk

  • Hero Member
  • *****
  • Posts: 5204
Re: SQL - looks easy but I cannot solve it
« Reply #17 on: April 01, 2023, 12:55:32 am »
Quote
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

  • Hero Member
  • *****
  • Posts: 1119
Re: SQL - looks easy but I cannot solve it
« Reply #18 on: April 01, 2023, 01:57:51 pm »
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

  • Hero Member
  • *****
  • Posts: 12976
Re: SQL - looks easy but I cannot solve it
« Reply #19 on: April 01, 2023, 03:27:34 pm »
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.
I actually get compliments for being rude... (well, Dutch, but that is the same)

Zvoni

  • Hero Member
  • *****
  • Posts: 1760
Re: SQL - looks easy but I cannot solve it
« Reply #20 on: April 01, 2023, 06:53:52 pm »
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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1205
Re: SQL - looks easy but I cannot solve it
« Reply #21 on: April 01, 2023, 10:19:08 pm »
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):-)
« Last Edit: April 01, 2023, 10:23:22 pm by af0815 »
regards
Andreas

Nicole

  • Hero Member
  • *****
  • Posts: 745
Re: SQL - looks easy but I cannot solve it
« Reply #22 on: April 03, 2023, 08:45:39 pm »
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

  • Hero Member
  • *****
  • Posts: 5204
Re: SQL - looks easy but I cannot solve it
« Reply #23 on: April 03, 2023, 08:57:53 pm »
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.

 

TinyPortal © 2005-2018