Date in the MySQL database from DateTimePicker
In DateTimePicker1 from MySQL, the date is formatted and flipped automatically by the query:
...DATE_FORMAT(`from_date`, "%d.%m.%Y") as `from_date`,...
But later, from DateTimePicker1, I can not turn it into MySQL format, i.e. turn it from 27.02.2001 in 2001-02-27, it does not automatically turn over there and it turns out 2027-02-20. I'm trying to do it in code:
ADOQuery1.SQL.add('UPDATE table_name SET `from_date`=DATE_FORMAT("'+DateToStr(DateTimePicker1.Date)+'",''%Y-%m-%d'') WHERE `number` = 1');
I tried doing DateTimePicker1.Format:='yyyy-M-d'; before the SQL query, but it didn't help.
Strange situation with the time format for different platforms - can't this planet decide on a single standard of time? Time, after all, is on a planet of the same calculus. In general, I moved away from the topic of the question.
What should I do? How to write a date from a datepicker to the database
2 answers
What should I do? How to write a date from a datepicker to the database
Option 1:
Explicitly specify the format by replacing DateToStr(DateTimePicker1.Date) with FormatDateTime('yyyy-mm-dd', DateTimePicker1.Date)
Option 2:
Explicitly specify the format by replacing DateToStr(DateTimePicker1.Date) with an overloaded version of DateToStr(DateTimePicker1.Date, MySQLFormatSettings)
In this case, you need to declare the variable MySQLFormatSettings: TFormatSettings in advance and set its date format, and, preferably, a decimal separator.
Option 3:
Use parametric request
ADOQuery1.SQL.add('UPDATE table_name SET `from_date`=:Date1 WHERE `number` = 1');
ADOQuery1.ParamByName('Date1').AsDate := DateTimePicker1.Date;
PS
Strange situation with the time format for different platforms
For internal representation in programs, ISO 8601 is increasingly used: YYYY-MM-DD It is the most versatile.
Can't this planet decide on a single standard of time?
No, it can't. People are too attached to their history and culture.
Time, after all, is on a planet of the same calculus.
You are somewhat not aware of
You should avoid converting strings to dates and vice versa.
DateTimePicker1 has a property DateTimе that points to the entered value.
In requests, you must specify parameters wherever possible. This will remove problems with conversion and type checking. It also allows the database to better optimize queries.
ADOQuery1.Parameters.ParamByName('family').Value := DateTimePicker1.DateTimе;
The parameter must be of type ftDateTime, and the request itself will change to
UPDATE table_name
SET `from_date`= :p_date
WHERE `number` = :p_num