Page 1 of 1
Date-format on client differs from format on server
Posted: Mon 18 Apr 2011 15:33
by formi
I installed an MS SQL Server 2008 on a Win7-64Bit machine to develop my application. I created a table with a date-field. If I show the fields on that machine it displays the date in the format I want to have (dd.mm.yyyy). If I make the same query on a client I get the fields with format yyyy-dd-mm (with SELECT * FROM myTable).
Where have I to change the format?
Maybe I have to install some SQL-stuff on the client. I didn't install anything (only the SQL-Server on the develop-machine)
Thanks for your help, peter
Posted: Tue 19 Apr 2011 08:31
by AndreyZ
Hello,
SDAC is a set of non-visual components, and they don't serve for displaying data in VCL visual components. SDAC transfers date values to visual components in the TDateTime format (double). Delphi uses date format that is specified on a client machine. You can use the following code to obtain date in the format you want:
Code: Select all
MSQuery.Open;
ShowMessage(FormatDateTime('dd.mm.yyyy', MSQuery.FieldByName('MyDate').AsDateTime));
Also you can use the ShortDateFormat Delphi variable in the following way:
Code: Select all
ShortDateFormat := 'dd.mm.yyyy';
MSQuery.Open;
ShowMessage(MSQuery.FieldByName('MyDate').AsString);
ShortDateFormat is the format string used to convert date value to a short string suitable for editing. For more information, please refer to the Delphi help.
Posted: Wed 20 Apr 2011 10:05
by formi
Thanks for your answer. I made this but with no success:
- on the pc with SQL Server installed it works fine (I can change the formats)
- in the clients it doesn't work, always same format
As I just sayed I didn't install anything on the clients. Is that ok or have I to install an sql-client? I think it is included in Win7, otherwise I couldn't access anything.
Peter
Posted: Wed 20 Apr 2011 14:00
by AndreyZ
Please try creating a new application with one button on a form, and write the following code:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
ShowMessage(FormatDateTime('dd.mm.yyyy', now));
end;
After this check if this application shows the date value in the correct string format on your computer and on a client computer.
Posted: Wed 20 Apr 2011 15:02
by formi
Result of the test:
Server: with ShowMessage(...) is 20.04.2011, with SQL 20.04.2011
Client: with ShowMessage(...) is 20.04.2011, with SQL is 2011.04.20
If you giv me an EMail-Adress I can send you PrintScreens
Regards, Peter[/img]
Posted: Thu 21 Apr 2011 09:03
by AndreyZ
Maybe you are using persistent fields and the DisplayFormat property to show dates in the yyyy-dd-mm format. If it's true, please set the DisplayFormat property for your date fields to the blank value.
Also you can try creating another application with the TButton, TMSConnection, TMSQuery, TMSDataSource, and TDBGrid components, and use the following code:
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
begin
ShortDateFormat := 'dd.mm.yyyy';
MSConnection1.Server := 'server';
MSConnection1.Database := 'database';
MSConnection1.Username := 'username';
MSConnection1.Password := 'password';
MSConnection1.LoginPrompt := False;
MSQuery1.Connection := MSConnection1;
MSQuery1.SQL.Text := 'select * from MyTable'; // MyTable contains date values
MSDataSource1.DataSet := MSQuery1;
DBGrid1.DataSource := MSDataSource1;
MSQuery1.Open;
end;
This code will force TDBGrid to show date values in the dd.mm.yyyy format regardless of the date format on a client machine. Please check that you are using the same approach in your application.
Posted: Thu 21 Apr 2011 10:08
by formi
I'm sorry, I tried it but its the same result. The main problem is that I have a BDE-application which I convert to MS SQL. In the field-editor of the table there are all fields listed. If I make a select on that table I get (only client) the error "different datatypes for field 'MyDate'. Required "Date", found "WideString". And that is because that format is wrong.
Posted: Thu 21 Apr 2011 10:42
by AndreyZ
BDE and SDAC create different field types. To avoid the "different datatypes" error, you should remove all persistent fields and add them again.
Posted: Thu 21 Apr 2011 11:18
by formi
Yes I know and I changed this fields - but the error is the same.
I changed the sample you gave me: adding a TMsTable, adding the fields in the fieldsEditor --> same "WideString"-error
Posted: Thu 21 Apr 2011 14:50
by AndreyZ
SDAC creates TDateTimeField fields for DATETIME columns and TDateField fields for DATE columns. Please check that all DATETIME and DATE fields have correct field types. If it doesn't help, please send a script to create a table with which you encountered the problem to andreyz*devart*com.
Posted: Thu 12 May 2011 14:35
by Ignaz Wrobel
in case this has not been resolved yet:
I observed the same problem, the "WideString" datatype error für datetime DB-Fields; after removing the old version of SQL Server Native Client and installing the version for SQLServer 2008, the problem was resolved
Posted: Fri 13 May 2011 06:34
by formi
Yes I solved it - I installed the native client. Thank you for your answer, Peter