Board index » delphi » American dates Grr!

American dates Grr!

Quote
Troy Tarrant wrote:
> Hi all,

> even though I set my regional settings to date format dd/mm/yyyy and my bde
> settings to mode 1 for dates (d/m/y) I still can't enter SQL in a TQuery
> object without first converting the date to mm/dd/yyyy.

> It seems no matter what I do the BDE always wants american dates !

> What am I doing wrong ?

> Why doesn't the BDe accept dates in dd MMM yyyy format ? This format is so
> easy and clear to understand.

> Thanks in advance

> Troy

Troy,

Delphi help states that Local SQL expects dates to be allways on US date
format, either MM/DD/YY  or MM/DD/YYYY.

Victor H. Ulloa
Tierra Fertil S.A.

 

Re:American dates Grr!


Hi all,

even though I set my regional settings to date format dd/mm/yyyy and my bde
settings to mode 1 for dates (d/m/y) I still can't enter SQL in a TQuery
object without first converting the date to mm/dd/yyyy.

It seems no matter what I do the BDE always wants american dates !

What am I doing wrong ?

Why doesn't the BDe accept dates in dd MMM yyyy format ? This format is so
easy and clear to understand.

Thanks in advance

Troy

Re:American dates Grr!


SQL dates are always in a fixed format which just happens to be mm/dd/yyyy.
I suppose that it is fixed because the SQL statement does not necessarily
execute on the client machine.  If it weren't a fixed format then you'd have
a problem if a client machine in London passed SQL to a server in the
States.

It's a good thing not a bad thing, guv.

Re:American dates Grr!


On Thu, 4 Mar 1999 16:44:42 +1300, "Troy Tarrant"

Quote
<Troy_Tarr...@royalsunlife.co.nz> wrote:
>even though I set my regional settings to date format dd/mm/yyyy and my bde
>settings to mode 1 for dates (d/m/y) I still can't enter SQL in a TQuery
>object without first converting the date to mm/dd/yyyy.

>It seems no matter what I do the BDE always wants american dates !

>What am I doing wrong ?

You are not doing anything wrong. This is simply the way local SQL works.
It is not localized for various international date formats. The date
portions of DATE and TIMESTAMP literal values must be in the US form (month
first, day second).

Column and parameter values are not affected by this. So if you wish to use
date values in SQL and not have to worry about the format, use parameters
instead of literals.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:American dates Grr!


The following function converts a DateTime to a string that SQL
recognizes for date calculations:

function qdate(const ThisDate: TDateTime): string;
var
 Year, Month, Day: Word;
begin
 try
  DecodeDate(ThisDate, Year, Month, Day);
  Result:= pad_zero(IntStr(Day),2) + '-';
  case Month of
   1:  Result:= Result + 'JAN';
   2:  Result:= Result + 'FEB';
   3:  Result:= Result + 'MAR';
   4:  Result:= Result + 'APR';
   5:  Result:= Result + 'MAY';
   6:  Result:= Result + 'JUN';
   7:  Result:= Result + 'JUL';
   8:  Result:= Result + 'AUG';
   9:  Result:= Result + 'SEP';
   10: Result:= Result + 'OCT';
   11: Result:= Result + 'NOV';
   12: Result:= Result + 'DEC';
  end;
  Result:= Result + '-' + IntStr(Year);
  Result:= QuotedStr(Result)
 except
  Result:= ''
 end
end;

function pad_zero(const s: string; const n: integer): string;
begin
 Result:= s;
 if length(Result) < n then
 repeat
  Result:= zero + Result
 until length(Result) = n
end;

Quote
Steve Koterski wrote in message

<36dfc5c6.4256...@forums.inprise.com>...
Quote
>On Thu, 4 Mar 1999 16:44:42 +1300, "Troy Tarrant"
><Troy_Tarr...@royalsunlife.co.nz> wrote:

>>even though I set my regional settings to date format dd/mm/yyyy and
my bde
>>settings to mode 1 for dates (d/m/y) I still can't enter SQL in a
TQuery
>>object without first converting the date to mm/dd/yyyy.

>>It seems no matter what I do the BDE always wants american dates !

>>What am I doing wrong ?

>You are not doing anything wrong. This is simply the way local SQL
works.
>It is not localized for various international date formats. The date
>portions of DATE and TIMESTAMP literal values must be in the US form
(month
>first, day second).

>Column and parameter values are not affected by this. So if you wish
to use
>date values in SQL and not have to worry about the format, use
parameters
>instead of literals.

>/////////////////////////////////////////////////////////////////////
/////
>Steve Koterski                    "My problem lies in reconciling my
gross
>Technical Publications            habits with my net income."
>INPRISE Corporation                             -- Errol Flynn
(1909-1959)
>http://www.borland.com/delphi

Re:American dates Grr!


Steve Koterski p?e ve zprv <36dfc5c6.4256...@forums.inprise.com>

Quote
>On Thu, 4 Mar 1999 16:44:42 +1300, "Troy Tarrant"
><Troy_Tarr...@royalsunlife.co.nz> wrote:

>>......
>>It seems no matter what I do the BDE always wants american dates !
>>.....

Quite interrestingly, things seem to be more complicated:
*Independenly* of BDE Config Date MODE and SEPARATOR settings
AND
*Independenly* of Windows national settings
when I use period (.) as date separator in local SQL date literals,
the date literal is always decoded as being in European (D.M.YYYY) format
WHEREAS
when I use slash (/) as date separator in local SQL date literals,
the date literal is always decoded as being in American (M.D.YYYY) format

Configuration: Language driver: Paradox ANSI Czech, BDE 5.01
--
Roman
(please remove 'stopspam' in header)
mail: i...@rksolution.cz
URL: www.rksolution.cz

Re:American dates Grr!


On Thu, 4 Mar 1999 14:56:47 -0500, "Sanford Aranoff" <saran...@nusinc.com>
wrote:

Quote
>The following function converts a DateTime to a string that SQL
>recognizes for date calculations:

>function qdate(const ThisDate: TDateTime): string;
>var
> Year, Month, Day: Word;
>begin
> try
>  DecodeDate(ThisDate, Year, Month, Day);
>  Result:= pad_zero(IntStr(Day),2) + '-';
>  case Month of
>   1:  Result:= Result + 'JAN';
>   2:  Result:= Result + 'FEB';
>   3:  Result:= Result + 'MAR';
>   4:  Result:= Result + 'APR';
>   5:  Result:= Result + 'MAY';
>   6:  Result:= Result + 'JUN';
>   7:  Result:= Result + 'JUL';
>   8:  Result:= Result + 'AUG';
>   9:  Result:= Result + 'SEP';
>   10: Result:= Result + 'OCT';
>   11: Result:= Result + 'NOV';
>   12: Result:= Result + 'DEC';
>  end;
>  Result:= Result + '-' + IntStr(Year);
>  Result:= QuotedStr(Result)
> except
>  Result:= ''
> end
>end;

That's a nice function, no doubt. But why did you go to all that work
<grin>? Just use a couple built-in functions to do it in one line.

  StrVar := AnsiUppercase(FormatDateTime('dd-mmm-yyyy', Date));

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:American dates Grr!


Hi Steve,

As I understand it, SQL expects dates in US format, which presumably
means English spelling of month abbreviations. However, as far as I can
see FormatDateTime uses either the ShortMonthNames array or the
LongMonthNames array and thus is locale-specific.

Cheers,
Carl

PS. It's way past my bedtime so I may well have missed something obvious
...

Quote
Steve Koterski wrote:

---8<---

> That's a nice function, no doubt. But why did you go to all that work
> <grin>? Just use a couple built-in functions to do it in one line.

>   StrVar := AnsiUppercase(FormatDateTime('dd-mmm-yyyy', Date));

Re:American dates Grr!


On Fri, 05 Mar 1999 02:40:31 +0000, Carl Caulkett <car...@dircon.co.uk>
wrote:

Quote
>As I understand it, SQL expects dates in US format, which presumably
>means English spelling of month abbreviations. However, as far as I can
>see FormatDateTime uses either the ShortMonthNames array or the
>LongMonthNames array and thus is locale-specific.

>PS. It's way past my bedtime so I may well have missed something obvious

I was merely commenting (in a friendly, nonjudemental way) on the overkill
the function represented. I was commenting only on the function and not on
any relationship with SQL. Certainly there may be situations not related to
SQL where such a date formatting is needed.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:American dates Grr!


You must understand that SQL is a language. And as most language, english
date format is in use (the same for numerics with decimals).
SQL date format is following an ANSI standard. This is not a software editor
whim...

Other Threads