Devart Forums

Posted: **Mon 19 Mar 2018 15:13**

I'm having a problem with the format of strings returned by TMyQuery for date fields in MySQL.

I created a test table using

Code: Select all

CREATE TABLE testtable ( letter CHAR(1) , date_date DATE, date_str VARCHAR(10) );
INSERT INTO testtable (letter, date_date, date_str) VALUES ('A', '2018-03-12', '2018-03-12');

Running the following SQL in my DBMS (SQLyog)

Code: Select all

SELECT  letter, date_date, date_str FROM  testtable;

produces, as expected

Code: Select all

letter   |    date_date     | date_str
'A'      |    '2018-03-12'  |    '2018-03-12'

However using a TMyconnection and a TMyQuery (both with default properties)in Delphi, the same query produces

Code: Select all

letter   |    date_date     | date_str
'A'      |    '12/03/2018'  |    '2018-03-12'

ie the field date_date is shown in a different format, which really mucks up further processing in the application. For example where the dates are put into a dropdown box for later selection and use in a WHERE clause., as that produces sql of the form

Code: Select all

SELECT  * FROM  testtable WHERE date_date = '12/03/2018';

which produces no results.

This can be shown by the delphi code

Code: Select all

var TheDate  : string;
begin
TMyQuery1.SQL.Clear;
TMyQuery1.SQL.Add('SELECT  date_date FROM  testtable;');
TMyQuery1.open   ;
TheDate := TMyQuery1.FieldByName('date_date').AsString  ;
ShowMessage(theDate);
end;

that will show '12/03/2018' is being held in the variable TheDate.

I realise that I can get the result I want by using CAST or CONVERT eg

Code: Select all

SELECT  CONVERT(date_date, char) FROM  testtable;

but where is the setting that is telling TMyQuery to convert the date to the format dd/mm/yyyy instead of leaving it in the yyyy-mm-dd format that was used when entering it ? (And why does my DBMS show a different, but as far as I am concerned a correct, result?)

Posted: **Tue 20 Mar 2018 08:11**

The question regarding dispalying the field value is not related to UniDAC functionality. If the DisplayFormat property value of the field is not set, then the field value is formatted based on the values of the global variable System.SysUtils.FormatSettings. You can set the required display format in this variable (for example, FormatSettings.LongDateFormat: = 'yyyy-mm-dd'). For more information on the fields of the FormatSettings variable, refer to the Embarcadero documentation: http://docwiki.embarcadero.com/Librarie ... atSettings.
MySQL recognizes DATE values as a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts: dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html. Therefore, to solve your task, you should use the following queries:

Code: Select all

SELECT * FROM testtable WHERE date_date = '2018/03/12';

or

Code: Select all

SELECT * FROM testtable WHERE date_date = '2018-03-12';

Devart Forums

TMyQuery alters format of date strings?

TMyQuery alters format of date strings?

Re: TMyQuery alters format of date strings?