Board index » delphi » dateformat (dd.mm.yyyy vs. mm.dd.yyyy)

dateformat (dd.mm.yyyy vs. mm.dd.yyyy)

Hi!

I'm programming with the BDE and a paradox.
I have a date field in one of my tables.
My native usage of a TDateTime is "dd.mm.yyyy" (icelandic)
The app must run on machines configured for other countries, modified in the
control panel (regional settings).

When I change my country from "Icelandic" to "English" and re-run my app,
the TDateTimePicker starts to show me the "mm.dd.yyyy" format, which is OK.
But when I try to update a database record, I get the error message:
"mm.dd.yyyy" is not a valid date".

What is the best way to handle those changes of date formats between
languages or is there a general rule?

Best regards,
Kolbeinn Sigurjonsson

 

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Kolbeinn Sigurjonsson <k...@net-album.com> skrev i en
nyhedsmeddelelse:8dp3al$k...@bornews.borland.com...

Quote
> Hi!

> I'm programming with the BDE and a paradox.
> I have a date field in one of my tables.
> My native usage of a TDateTime is "dd.mm.yyyy" (icelandic)
> The app must run on machines configured for other countries, modified in
the
> control panel (regional settings).

you have to change the tables languagedriver too!
Quote

> When I change my country from "Icelandic" to "English" and re-run my app,
> the TDateTimePicker starts to show me the "mm.dd.yyyy" format, which is
OK.
> But when I try to update a database record, I get the error message:
> "mm.dd.yyyy" is not a valid date".

> What is the best way to handle those changes of date formats between
> languages or is there a general rule?

> Best regards,
> Kolbeinn Sigurjonsson

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Quote
>When I change my country from "Icelandic" to "English" and re-run my app,
>the TDateTimePicker starts to show me the "mm.dd.yyyy" format, which is OK.
>But when I try to update a database record, I get the error message:
>"mm.dd.yyyy" is not a valid date".

What is the code you are using to update the database?
If you have a date field and you use

table.fieldbyName.asDateTime := somedatevalue
you should not have any problems.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


I have a similar problem with an SQL statement issued in a TQuery.
Eg. SELECT * FROM CLIENTS WITH DOB = '01-May-1998';
The date format matches the destop setting. I get a message something like:
"01-May-1998" is not a valid date time.
The same command run from the Windows ISQL command line works correctly.
I've tried various different date formats without success except for
mm/dd/yyyy format which is not good for a country which at a minimum uses
dd/mm/yyyy

My working theory is that the BDE interprets the date or date/time in it's
own expected format. The question is:
"What must be done to ensure that the BDE date format is the same as the
desktop region? Or is this not possible/easy?"

Is there some setup that could be done, say when starting the application
that would reset the BDE date format to conform with the desktop settings,
so that from that time on (or even if they must then restart PC if
necessary) the two were in sync?

Quote
Brian Bushay TeamB wrote in message <3906088b.17517512@floyd>...

>>When I change my country from "Icelandic" to "English" and re-run my app,
>>the TDateTimePicker starts to show me the "mm.dd.yyyy" format, which is
OK.
>>But when I try to update a database record, I get the error message:
>>"mm.dd.yyyy" is not a valid date".

>What is the code you are using to update the database?
>If you have a date field and you use

>table.fieldbyName.asDateTime := somedatevalue
>you should not have any problems.

>--
>Brian Bushay (TeamB)
>Bbus...@NMPLS.com

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Quote
>I have a similar problem with an SQL statement issued in a TQuery.
>Eg. SELECT * FROM CLIENTS WITH DOB = '01-May-1998';
>The date format matches the destop setting. I get a message something like:
>"01-May-1998" is not a valid date time.

SQL in BDE follows the SQL 92 standard that expects dates in mm/dd/yyyy format.
So you have a couple of options.  
1.  use a paramater of the TdateTime type

with Query1 do begin
  SQL.clear;
  SQL.add('SELECT * FROM CLIENTS WITH DOB = :myDate'
 ParambyName(myDate).asDateTime := StrToDate('01-May-1998')'

Or if you need to a string value you will have to convert your date to a
TdateTime value then use FormatDateTime or DateTimeToString to convert your date
to a string using the mm/dd/yyyy format to use in the query.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Thanks a lot Brian!
That explains my dilema.

Just on a slightly different note.
Does the SQL 92 format not know about the LIKE comparison?

I have been trying to issue "LIKE" style searches on client names but it
(the BDE I think) reports that the LIKE command is not recognised.
I have been putting it down to the fact that I'm using Delphi 4 and
Interbase 5.5 (and also on 5.6) thru the BDE 5.0.x as not supporting the
comparator. Is there a similar comparison for the BDE?

Thanks again in advance.

David Wright

Quote
Brian Bushay TeamB wrote in message <39044a7b.37249168@floyd>...

>>I have a similar problem with an SQL statement issued in a TQuery.
>>Eg. SELECT * FROM CLIENTS WITH DOB = '01-May-1998';
>>The date format matches the destop setting. I get a message something
like:
>>"01-May-1998" is not a valid date time.

>SQL in BDE follows the SQL 92 standard that expects dates in mm/dd/yyyy
format.
>So you have a couple of options.
>1.  use a paramater of the TdateTime type

>with Query1 do begin
>  SQL.clear;
>  SQL.add('SELECT * FROM CLIENTS WITH DOB = :myDate'
> ParambyName(myDate).asDateTime := StrToDate('01-May-1998')'

>Or if you need to a string value you will have to convert your date to a
>TdateTime value then use FormatDateTime or DateTimeToString to convert your
date
>to a string using the mm/dd/yyyy format to use in the query.

>--
>Brian Bushay (TeamB)
>Bbus...@NMPLS.com

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Quote
David Wright wrote in message <8dtohu$5...@bornews.borland.com>...

>Just on a slightly different note.
>Does the SQL 92 format not know about the LIKE comparison?

>I have been trying to issue "LIKE" style searches on client names but it
>(the BDE I think) reports that the LIKE command is not recognised.

"Like" is definitely ANSI standard SQL and is also definitely accepted by
the BDE's Local SQL. Let's see the statement that gets the error. Note that
Like is case-sensitive.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Quote
David Wright wrote in message <8dv56h$i...@bornews.borland.com>...
>The following is an extract from my code for the search routine that tries
>to allow the 'LIKE' comparison.

>QryClient.SQL.Text := 'select * from client where surname like '%SMI%'
order
>by full_name, client_id';

The above line will not even compile. However assuming you are putting
something there that does compile and is a valid statement, "like" shouldn't
be the problem.

I cannot reproduce this problem using the sample DBDEMOS.CUSTOLY.DB using as
close to identical statement as I can there and trying several variations
with request live both true and false and using both indexed and non-indexed
fields for the search and.order by. If you have RequestLive set true, try
setting it false and see if that makes any difference. If not then please
post the actual code that builds this SQL statement rather than pasting from
the de{*word*81}. What you've posted certainly appears to be ok, but there must
be something in there causing the problem.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


The following is an extract from my code for the search routine that tries
to allow the 'LIKE' comparison.

QryClient.Close;
QryClient.UnPrepare;
QryClient.SQL.Clear;
{The SQL command is taken straight from the variable during a debug session}
QryClient.SQL.Text := 'select * from client where surname like '%SMI%' order
by full_name, client_id';
QryClient.Open;

When running in developer mode I get the following error message:
Project MyApp.exe raised exception class error EDBEngineError with message
'Operation not applicable.'.
Otherwise I just get the 'Operation not applicable.' message.

I have tried the SQL command as Uppercase but with the same result.
NOTE: The same command works correctly when run in Windows ISQL window.

Any Ideas greatly appreciated.

Quote
Wayne Niddery (TeamB) wrote in message <3902fe47@dnews>...
>"Like" is definitely ANSI standard SQL and is also definitely accepted by
>the BDE's Local SQL. Let's see the statement that gets the error. Note that
>Like is case-sensitive.

Re:dateformat (dd.mm.yyyy vs. mm.dd.yyyy)


Quote
>QryClient.SQL.Text := 'select * from client where surname like '%SMI%' order
>by full_name, client_id';

Like is supported in SQL 92 and BDE's implementation.

Try using Quotes around your value

QryClient.SQL.Text := 'select * from client where surname like '
+quotedStr(%SMI%)+' order by full_name, client_id';

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads