Board index » delphi » Windows decimal- and thousand separator settings and formatting numeric fields

Windows decimal- and thousand separator settings and formatting numeric fields

I have problem with some of "my" users who have set on their machines
a "," for  decimal point and "." as thousand numeric separator.

At forming SQL query statement, I covert numeric parameter into a
string, but result of the query execution is an SQL error.  
The server interprets ,say, "8,25" as "8" and "25" instead of "8.25"
[which makes sense, in a way].

I see two possible solutions:
1. Change the user's settings,
2. After the conversion to string, reformat the string, i.e. replace
"," with "." and vise versa.  

Needless to say, I am not too keen on any of them; first wont be
welcomed by users and the second is a bit risky.

I would like to have "numeric-to-string" conversion with my predefined
characters for decimal and thousand separator instead of system's
settings.

"FormatFloat" doesn't seem to do the job - even with format string
"#0.00", it returns value with "," instead of "." .

How can I read DecimalSeparator and ThousandSeparator global variables
at the run time?

If you have any ideas or experience with the above, please help!

THANKS...

A Djekic
KSH Ltd.
Montreal, QC

 

Re:Windows decimal- and thousand separator settings and formatting numeric fields


This must be done on the numbers BEFORE you add them all up in a SQL
statement.

TreatString := IntToStr(MyNumber);
While Pos(',',TreatString)>0 do TreatString[Pos(',',TreatString)] := '.';

I imagine someone here will say that you can set the separators runtime
within you app, and you can.

But there is no guarantee that the user, or even more likely, another app
will perform the same change AFTER yours.

Roger

Quote
Aleksandra Djekic wrote in message <35abe0e1.35780...@forums.inprise.com>...
>I have problem with some of "my" users who have set on their machines
>a "," for  decimal point and "." as thousand numeric separator.

>At forming SQL query statement, I covert numeric parameter into a
>string, but result of the query execution is an SQL error.
>The server interprets ,say, "8,25" as "8" and "25" instead of "8.25"
>[which makes sense, in a way].

>I see two possible solutions:
>1. Change the user's settings,
>2. After the conversion to string, reformat the string, i.e. replace
>"," with "." and vise versa.

>Needless to say, I am not too keen on any of them; first wont be
>welcomed by users and the second is a bit risky.

>I would like to have "numeric-to-string" conversion with my predefined
>characters for decimal and thousand separator instead of system's
>settings.

>"FormatFloat" doesn't seem to do the job - even with format string
>"#0.00", it returns value with "," instead of "." .

>How can I read DecimalSeparator and ThousandSeparator global variables
>at the run time?

>If you have any ideas or experience with the above, please help!

>THANKS...

>A Djekic
>KSH Ltd.
>Montreal, QC

Re:Windows decimal- and thousand separator settings and formatting numeric fields


Have you try the following:

Application.UpdateFormatSettings := false;
ShortDateFormat := 'MM/dd/yyyy';
DateSeparator := '/';
DecimalSeparator := '.';
ThousandSeparator

etc.

when you program first starts?  They are functions in the SysUtils unit,
and they should set the appropriate settings for your program.

Below is from Delphi3 Help files:

Unit

SysUtils
The following are a set of variables used to define the format for date and
time strings:

var CurrencyString: string;
var CurrencyFormat: Byte;
var NegCurrFormat: Byte;
var ThousandSeparator: Char;
var DecimalSeparator: Char;
var CurrencyDecimals: Byte;
var DateSeparator: Char;
var ShortDateFormat: string;
var LongDateFormat: string;
var TimeSeparator: Char;
var TimeAMString: string;
var TimePMString: string;
var ShortTimeFormat: string;

var LongTimeFormat: string;
var ShortMonthNames: array[1..12] of string;
var LongMonthNames: array[1..12] of string;
var ShortDayNames: array[1..7] of string;
var LongDayNames: array[1..7] of string;

Description

The SysUtils unit includes a number of variables that are used by the date
and time routines. Values are assigned to these variables to define the
formats of date and time strings. The initial values of these variables are
fetched from the system registry using the GetLocaleInfo function in the
Win32 API. Delphi VCL (non-console) applications will automatically update
these formatting variables in response to WM_WININICHANGE messages.
Application.UpdateFormatSettings will either allow or not allow changes in
system settings. The default is True. Set this property to False to prevent
the system settings from changing.

The description of each variable specifies the LOCALE_XXXX constant used to
fetch the initial value using the GetLocaleInfo Win32 API.

Typed constant  Defines
CurrencyString  CurrencyString defines the currency symbol (or characters)
used in floating-point to decimal conversions. The initial value is fetched
from LOCALE_SCURRENCY.
CurrencyFormat  CurrencyFormat defines the currency symbol placement and
separation used in floating-point to decimal conversions. Possible values
are:0 = '$1'1 = '1$'2 = '$ 1'3 = '1 $'The initial value is fetched from
LOCALE_ICURRENCY.
NegCurrFormat   NegCurrFormat defines the currency format used in
floating-point to decimal conversions of negative numbers. Possible values
are:0 = ($1)    4 = (1$)    8 = -1 $    12 = $ -11 = -$1     5 = -1$     9
= -$ 1    13 = 1- $2 = $-1     6 = 1-$    10 = 1 $-    14 = ($ 1)3 = $1-  
 7 = 1$-    11 = $ 1-    15 = (1 $)The initial value is fetched from
LOCALE_INEGCURR.
ThousandSeparator       ThousandSeparator is the character used to separate
thousands in numbers with more than three digits to the left of the decimal
separator. The initial value is fetched from LOCALE_STHOUSAND.
DecimalSeparator        DecimalSeparator is the character used to separate the
integer part from the fractional part of a number. The initial value is
fetched from LOCALE_SDECIMAL.
CurrencyDecimals        CurrencyDecimals is the number of digits to the right of
the decimal point in a currency amount. The initial value is fetched from
LOCALE_ICURRDIGITS.
DateSeparator   DateSeparator is the character used to separate the year,
month, and day parts of a date value. The initial value is fetched from
LOCATE_SDATE.
ShortDateFormat ShortDateFormat is the format string used to convert a date
value to a short string suitable for editing. For a complete description of
date and time format strings, refer to the documentation for the
FormatDateTime function. The short date format should only use the date
separator character and the  m, mm, d, dd, yy, and yyyy format specifiers.
The initial value is fetched from LOCALE_SSHORTDATE..
LongDateFormat  LongDateFormat is the format string used to convert a date
value to a long string suitable for display but not for editing. For a
complete description of date and time format strings, refer to the
documentation for the FormatDateTime function. The initial value is fetched
from LOCALE_SLONGDATE.
TimeSeparator   TimeSeparator is the character used to separate the hour,
minute, and second parts of a time value. The initial value is fetched from
LOCALE_STIME.
TimeAMString    TimeAMString is the suffix string used for time values between
00:00 and 11:59 in 12-hour clock format. The initial value is fetched from
LOCALE_S1159.
TimePMString    TimePMString is the suffix string used for time values between
12:00 and 23:59 in 12-hour clock format. The initial value is fetched from
LOCALE_S2359.
ShortTimeFormat ShortTimeFormat is the format string used to convert a time
value to a short string with only hours and minutes. The default value is
computed from LOCALE_ITIME and LOCALE_ITLZERO.
LongTimeFormat  LongTimeFormat is the format string used to convert a time
value to a long string with hours, minutes, and seconds. The default value
is computed from LOCALE_ITIME and LOCALE_ITLZERO.
ShortMonthNames ShortMonthNames is the array of strings containing short
month names. The mmm format specifier in a format string passed to
FormatDateTime causes a short month name to be substituted. The default
values are fecthed from the LOCALE_SABBREVMONTHNAME system locale entries.
LongMonthNames  LongMonthNames is the array of strings containing long month
names. The mmmm format specifier in a format string passed to
FormatDateTime causes a long month name to be substituted. The default
values are fecthed from the LOCALE_SMONTHNAME system locale entries.
ShortDayNames   ShortDayNames is the array of strings containing short day
names. The ddd format specifier in a format string passed to FormatDateTime
causes a short day name to be substituted. The default values are fecthed
from the LOCALE_SABBREVDAYNAME system locale entries.
LongDayNames    LongDayNames is the array of strings containing long day
names. The dddd format specifier in a format string passed to
FormatDateTime causes a long day name to be substituted. The default values
are fecthed from the LOCALE_SDAYNAME system locale entries.

Good luck.
Gene

Aleksandra Djekic <adje...@ksh.ca> wrote in article
<35abe0e1.35780...@forums.inprise.com>...

Quote
> I have problem with some of "my" users who have set on their machines
> a "," for  decimal point and "." as thousand numeric separator.

> At forming SQL query statement, I covert numeric parameter into a
> string, but result of the query execution is an SQL error.  
> The server interprets ,say, "8,25" as "8" and "25" instead of "8.25"
> [which makes sense, in a way].

> I see two possible solutions:
> 1. Change the user's settings,
> 2. After the conversion to string, reformat the string, i.e. replace
> "," with "." and vise versa.  

> Needless to say, I am not too keen on any of them; first wont be
> welcomed by users and the second is a bit risky.

> I would like to have "numeric-to-string" conversion with my predefined
> characters for decimal and thousand separator instead of system's
> settings.

> "FormatFloat" doesn't seem to do the job - even with format string
> "#0.00", it returns value with "," instead of "." .

> How can I read DecimalSeparator and ThousandSeparator global variables
> at the run time?

> If you have any ideas or experience with the above, please help!

> THANKS...

> A Djekic
> KSH Ltd.
> Montreal, QC

Re:Windows decimal- and thousand separator settings and formatting numeric fields


Have you try the following:

ShortDateFormat := 'MM/dd/yyyy';
DateSeparator := '/';
DecimalSeparator := '.';

etc.

when you program first starts?  They are functions in the SysUtils unit,
and they should set the appropriate settings for your program.

Good luck.
Gene

Roger Arnesen <w...@cares.kom> wrote in article
<6ogq6i$4i...@forums.borland.com>...

Quote
> This must be done on the numbers BEFORE you add them all up in a SQL
> statement.

> TreatString := IntToStr(MyNumber);
> While Pos(',',TreatString)>0 do TreatString[Pos(',',TreatString)] := '.';

> I imagine someone here will say that you can set the separators runtime
> within you app, and you can.

> But there is no guarantee that the user, or even more likely, another app
> will perform the same change AFTER yours.

> Roger

> Aleksandra Djekic wrote in message

<35abe0e1.35780...@forums.inprise.com>...

- Show quoted text -

Quote
> >I have problem with some of "my" users who have set on their machines
> >a "," for  decimal point and "." as thousand numeric separator.

> >At forming SQL query statement, I covert numeric parameter into a
> >string, but result of the query execution is an SQL error.
> >The server interprets ,say, "8,25" as "8" and "25" instead of "8.25"
> >[which makes sense, in a way].

> >I see two possible solutions:
> >1. Change the user's settings,
> >2. After the conversion to string, reformat the string, i.e. replace
> >"," with "." and vise versa.

> >Needless to say, I am not too keen on any of them; first wont be
> >welcomed by users and the second is a bit risky.

> >I would like to have "numeric-to-string" conversion with my predefined
> >characters for decimal and thousand separator instead of system's
> >settings.

> >"FormatFloat" doesn't seem to do the job - even with format string
> >"#0.00", it returns value with "," instead of "." .

> >How can I read DecimalSeparator and ThousandSeparator global variables
> >at the run time?

> >If you have any ideas or experience with the above, please help!

> >THANKS...

> >A Djekic
> >KSH Ltd.
> >Montreal, QC

Re:Windows decimal- and thousand separator settings and formatting numeric fields


On Tue, 14 Jul 1998 22:56:16 GMT, adje...@ksh.ca (Aleksandra Djekic)
wrote:

Quote
>I have problem with some of "my" users who have set on their machines
>a "," for  decimal point and "." as thousand numeric separator.

>At forming SQL query statement, I covert numeric parameter into a
>string, but result of the query execution is an SQL error.  
>The server interprets ,say, "8,25" as "8" and "25" instead of "8.25"
>[which makes sense, in a way].

>I see two possible solutions:
>1. Change the user's settings,
>2. After the conversion to string, reformat the string, i.e. replace
>"," with "." and vise versa.  

>Needless to say, I am not too keen on any of them; first wont be
>welcomed by users and the second is a bit risky.

Been there, seen it, done it! Don't even bother trying, use parameters
instead.. Make your statement like:

with Query do begin
  Close ;
  SQL.Text := 'SELECT name, address FROM customer WHERE customer_ID =
:CUSTID ' ;
  ParamByName('CUSTID').AsInteger := TheValue ;
  Open ;
end;

Always always always. Because you're never sure what the result of
IntToStr( 1000000 ) may be, it can be '1,000,000' or something. And
that is something you don't want in your SQLs. Even strings have a
problem: have you ever tried to make a SQL statement from a string
variable containing one single quote?

Par := 'It''s me' ;
Query.SQL.Text := 'SELECT person FROM persons WHERE description = ' +
Par ;
Query.Open ; //Will give you an error

This will work:

Par := 'It''''s me' ; //duplicate each single quote
Query.SQL.Text := 'SELECT person FROM persons WHERE description = ' +
Par ;
Query.Open ; //No problemo

Again, if you had used parameters:

Par := 'It''s me' ;
Query.SQL.Text := 'SELECT person FROM persons WHERE description =
:DESCR ' ;
Query.ParamByName('DESCR').AsString := Par ;
Query.Open ;

No problemo! Agreed, it's a little extra work but it's well worth it,
especially with stuff like date/time fields and floats.

Enjoy Delphi
Bart

Re:Windows decimal- and thousand separator settings and formatting numeric fields


Quote
>Been there, seen it, done it! Don't even bother trying, use parameters
>instead.. Make your statement like:

My suggestion : In addition to TFields beeing EVIL, Parameters in SQL
statements are also EVIL!

Why?  Because one day you will encounter a customer who says "We do not
accept one-user logon for all users on our database, and we certainly do not
accept synonyms."

Then you need the owner name in from of the table names in all queries.
Then you need to do it in code.  Then parameters becomes the haunt from
hell.

I've also been there, seen it, done it and even bought the t-shirt.

Roger

Other Threads