Date-format on client differs from format on server

formi · Post by **formi** » Mon 18 Apr 2011 15:33

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

AndreyZ · Post by **AndreyZ** » Tue 19 Apr 2011 08:31

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.

formi · Post by **formi** » Wed 20 Apr 2011 10:05

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

AndreyZ · Post by **AndreyZ** » Wed 20 Apr 2011 14:00

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.

formi · Post by **formi** » Wed 20 Apr 2011 15:02

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]

AndreyZ · Post by **AndreyZ** » Thu 21 Apr 2011 09:03

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.

formi · Post by **formi** » Thu 21 Apr 2011 10:08

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.

AndreyZ · Post by **AndreyZ** » Thu 21 Apr 2011 10:42

BDE and SDAC create different field types. To avoid the "different datatypes" error, you should remove all persistent fields and add them again.

formi · Post by **formi** » Thu 21 Apr 2011 11:18

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

AndreyZ · Post by **AndreyZ** » Thu 21 Apr 2011 14:50

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.

Ignaz Wrobel · Post by **Ignaz Wrobel** » Thu 12 May 2011 14:35

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

formi · Post by **formi** » Fri 13 May 2011 06:34

Yes I solved it - I installed the native client. Thank you for your answer, Peter