SDAC String DateTime Format / Delphi5

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

SDAC String DateTime Format / Delphi5

Post by dpoulin » Thu 03 Mar 2011 16:14

We are now using your SDAC component to bypass the BDE. This is great except that in the old BDE, there was a setting to select the date format. This setting was mainly used when accessing a date via the .asString function. For instance;

MyQuery.FieldByName('MyDate').asString

With your component, this function uses the regional settings, thus, depending on the user
to set it properly. We want to be able to let the customer uses whatever regional setting he might like, independant of the result of our code.

Can you expose the function to convert the date into a string so we can control it ourselve please? Trying to change all of our code everywhere is not something we can really do without causing several bugs.

Thanks you!

AndreyZ

Post by AndreyZ » Fri 04 Mar 2011 09:35

Hello,

SDAC uses Delphi functionality for the AsString method. You can use the following code to obtain date in the format you like:

Code: Select all

MSQuery.Open;
ShowMessage(FormatDateTime('mm.dd.yyyy', MSQuery.FieldByName('MyDate').AsDateTime));
Also you can use the ShortDateFormat Delphi variable in the following way:

Code: Select all

ShortDateFormat := 'mm.dd.yyy';
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.

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

Post by dpoulin » Fri 04 Mar 2011 13:38

So by showing us how to code it in Delphi, your are saying that it isn't possible to set a date format in SDAC ?
We know how to manage it in delphi but we have a huge software which was using the BDE format date without having to figure what is the customer regional settings.
Something like it could be set in the connection component.
Is there any way to do this in the component.

AndreyZ

Post by AndreyZ » Fri 04 Mar 2011 15:18

You don't need to determine the regional settings your customers have. Delphi automatically uses regional settings of a current computer for formatting date values to string values. Only if you don't want to use regional settings of your customers' computers, you should use suggestions specified above.

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

Post by dpoulin » Fri 04 Mar 2011 16:34

That's the point, I want my delphi to use the personnal computer regional settings but I don't want SDAC to use them

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

Post by dpoulin » Fri 04 Mar 2011 16:40

When the customer lives in honk kong, the date format must not be like here. We cannot decide what our customers wan't to see. If he wan't the date format to be showed as yyy*d*mm it is is own choice.
Our program is to big to be patched to manage the new behavior. By exposing the AsString function of your query component, we could change it very easily without being scared of somewhere in our program at the line 902465022 we forgot to patch our code to follow the new SDAC behavior.

AndreyZ

Post by AndreyZ » Wed 09 Mar 2011 10:25

SDAC are Delphi components and they use its functionality to convert date values into string format. It's not correct to make components and IDE to use different string formats of date values. Please describe in details the way in which we should implement such functionality in SDAC.

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

Post by dpoulin » Wed 09 Mar 2011 13:52

Why it isn't correct ?
I would say it shouldn't but not correct I do not understand why.
Advantage DataBase server does it and I haven't looked for more but I'm sure some other component does it.
We migrate from BDE to SDAC and BDE was doing it. This function was great for input date in a string format. This way, you will always be sure your date is working.
Maybe that was not the best way to do it, but it was possible to do it.
So now we are stuck with this behavior. Like I said before, our program is big, and we will have to do a lot of research to be sure every input date is in delphi dateformat (.AsDateTime) before inserting in the DB.
If SDAC cannot and will never handle a Date format,
our solution might be to find a component which does it.
I'll wait for your answer.
Regards

AndreyZ

Post by AndreyZ » Thu 10 Mar 2011 10:21

Please try composing a small sample with BDE components to demonstrate how BDE and Delphi work with different date formats, and send it to andreyz*devart*com.

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

Post by dpoulin » Thu 10 Mar 2011 15:41

The demo app is in process

Image

Uploaded with ImageShack.us

BDE System date settings HELP DOC

The System Formats Date settings specify how to convert string values
into date values.
To display these settings:

1. Click the Configuration tab in the left pane and expand Configuration in the settings tree.
2. Expand System in the tree.
3. Expand Formats in the tree.
4. Choose Date in the tree.
The settings appear on the Definition tab in the right pane.

In the following table, Parameter lists all the configurable date parameters, with their current values. When your BDE application is first installed, all values are set to their defaults.

Description briefly notes the purpose of the selected parameter.

To change a setting, highlight the desired parameter. Then replace its old value with a new value. When edits are complete, choose Object|Apply or click the Apply button to save the changes.

Parameter Description
SEPARATOR Character used to separate the month, day, and year components of a date value; such as, the "/" in "12/31/96."The default is the character normally used in the country selected in the Windows Control Panel when any BDE application is installed.

MODE Controls the order of the month, day, and year components and can be 0 (for MDY), 1 (for DMY), or 2 (YMD).The default is the order normally used in the country selected in the Windows Control Panel when any BDE application is installed.

FOURDIGITYEAR Determines how the BDE treats the century portion of a date when only the last two digits of a year are specified. If set to FALSE, the century part of the date’s year is added automatically based on its relative position in a baseline range. If the date falls between 01/01/00 and 12/31/49, the year is considered to be in the 21st century (05/20/22 becomes 05/20/2022). If the date falls between 01/01/50 and 12/31/99, the date is considered as being in the 20th century (12/08/98 becomes 12/08/1998). A TRUE setting has no effect on dates expressed with a century (the year of a new date of 12/30/1902 remains 1902).If set to TRUE, the year for the date is assumed to be literal (no century digits automatically prefixed to the date). For example, a date expressed as 07/72/96 is considered to be the year 96 (0096).FOURDIGITYEAR has an effect in such places as date literals in SQL statements.Default value: FALSE.

YEARBIASED Tells the BDE application whether or not it should add the century to years entered as two digits. For example, if TRUE and you enter "7/21/96," the BDE application interprets your value as "7/21/1996". If set to FALSE, the date is interpreted as entered (in this case, "7/21/0096"). YEARBIASED uses the same range considerations as
FOURDIGITYEAR for determining the century used.Default value: TRUE.

LEADINGZEROM Specifies whether or not single digit month values have a leading zero. For example, if you enter "1/1/80" and this is set to TRUE, the BDE application interprets the date as "01/1/80." If FALSE, the value is "1/1/80."Default value: FALSE

LEADINGZEROD Controls whether or not single digit day values have a leading zero. For example, if you enter "1/1/80" and this is set to TRUE, the BDE application interprets the value as "1/01/80." If FALSE, your date is "1/1/80."Default value: FALSE

AndreyZ

Post by AndreyZ » Fri 11 Mar 2011 10:05

We have checked BDE functionality with SQL Server. BDE maps Date columns as TStringField and that's why you can input date value in different formats. SDAC maps Date columns as TDateField which checks if input date value matches current regional settings.
SDAC is a set of non-visual components that provide data access, and they aren't intended to manage the way visual components format column values. SDAC transfers date values from the server to visual components as TDate type (double), and the way of formatting of these values is fully defined by visual components. For example, you can create persistent fields (using Fields Editor) and use the FormatDisplay property to control formatting of these fields for displaying. Here is a code example:

Code: Select all

MSQueryDateFld.DisplayFormat := 'mm.dd.yyyy'; // where MSQueryDateFld is a persistent TDate field
For more information about this property, please read the Delphi documentation.
For the time being we aren't planning to support such functionality, as we didn't have requests concerning such support. If we have enough requests concerning such support, we will investigate this question.

Post Reply