Board index » delphi » Problem with Access Date-Times in non-US locale

Problem with Access Date-Times in non-US locale

Hi.

I am using D6 ADO with Access 2000 database.

I construct some SQL, appending the following to the command text as part of
the 'where' clause:

      dt1 := DateOf(EdtFromDate.date) + TimeOf(EdtFromTime.time);
      dt2 := DateOf(EdtToDate.date) + TimeOf(EdtToTime.time);
      TempCommandText := TempCommandText + ' and EntryDate <= #' +
FormatDateTime(DbDateFormat, dt2) + '#';
      TempCommandText := TempCommandText + ' and EntryDate >= #' +
FormatDateTime(DbDateFormat, dt1) + '#';

DBDateFormat is a constant given by           DBDateFormat = '#yyyy-mm-dd#';
edtFromDate etc are the obvious edit controls with DateTime values for the
start and end of the search

I am in an Australian locale, and this command text seems to sometimes swap
the date and month so that Access gets confused and returns say no records
when it should get 2. But not all the time. Just sometimes - it was working
100% ok today for example. The use of DBDateTimeFormat was meant to overcome
that locale problem, but apparently there is more going on.

Any suggestions on how to sort this problem? Any help appreciated.

Thanks,

Lauchlan M.

 

Re:Problem with Access Date-Times in non-US locale


Try using Parameters to store the datetime values, this should overcome date
format problems (month positions and separators).

--
Mike Collier BSc (Hons) - www.adoanywhere.com
ADO Object Browser and COM Server.
Consultancy and Design - ADO & Database Specialists.

Quote
"Lauchlan M" <LMackin...@Hotmail.com> wrote in message

news:3d57aaf6_2@dnews...
Quote
> Hi.

> I am using D6 ADO with Access 2000 database.

> I construct some SQL, appending the following to the command text as part
of
> the 'where' clause:

>       dt1 := DateOf(EdtFromDate.date) + TimeOf(EdtFromTime.time);
>       dt2 := DateOf(EdtToDate.date) + TimeOf(EdtToTime.time);
>       TempCommandText := TempCommandText + ' and EntryDate <= #' +
> FormatDateTime(DbDateFormat, dt2) + '#';
>       TempCommandText := TempCommandText + ' and EntryDate >= #' +
> FormatDateTime(DbDateFormat, dt1) + '#';

> DBDateFormat is a constant given by           DBDateFormat =
'#yyyy-mm-dd#';
> edtFromDate etc are the obvious edit controls with DateTime values for the
> start and end of the search

> I am in an Australian locale, and this command text seems to sometimes
swap
> the date and month so that Access gets confused and returns say no records
> when it should get 2. But not all the time. Just sometimes - it was
working
> 100% ok today for example. The use of DBDateTimeFormat was meant to
overcome
> that locale problem, but apparently there is more going on.

> Any suggestions on how to sort this problem? Any help appreciated.

> Thanks,

> Lauchlan M.

Re:Problem with Access Date-Times in non-US locale


Quote
"Mike Collier" <m...@adoanywhere.com> wrote in message

news:3d57b465_1@dnews...

Quote
> Try using Parameters to store the datetime values, this should overcome
date
> format problems (month positions and separators).

Mike,

I think I do.

eg

datamodulemain.cmdInsertNewSessionData.Parameters.ParamValues['EntryDate']
:= Now();

EntryDate is what I'm searching over in the SQL mentioned previously.

Lauchlan M.

Re:Problem with Access Date-Times in non-US locale


Thanks Vassiliev.

Do you know why Access might be flaky on this issue - work sometimes but not
others? It seems to be intermittent about working or not when I took steps
that seem to be pretty similar to what you describe.

Regards,

Lauchlan Mackinnon.

Re:Problem with Access Date-Times in non-US locale


an alternative of course  which is not locale dependant is to pass a number

eg
var
   timus:variant;
begin
  timus:=int(Now);
  Dm.Q_D1.SQL.Text:='select * from table where filter_text'+
                                     ' and date_field  = '+IntToStr(timus);
end;

Dominic

Quote
"Lauchlan M" <LMackin...@Hotmail.com> wrote in message

news:3d57aaf6_2@dnews...
Quote
> Hi.

> I am using D6 ADO with Access 2000 database.

> I construct some SQL, appending the following to the command text as part
of
> the 'where' clause:

>       dt1 := DateOf(EdtFromDate.date) + TimeOf(EdtFromTime.time);
>       dt2 := DateOf(EdtToDate.date) + TimeOf(EdtToTime.time);
>       TempCommandText := TempCommandText + ' and EntryDate <= #' +
> FormatDateTime(DbDateFormat, dt2) + '#';
>       TempCommandText := TempCommandText + ' and EntryDate >= #' +
> FormatDateTime(DbDateFormat, dt1) + '#';

> DBDateFormat is a constant given by           DBDateFormat =
'#yyyy-mm-dd#';
> edtFromDate etc are the obvious edit controls with DateTime values for the
> start and end of the search

> I am in an Australian locale, and this command text seems to sometimes
swap
> the date and month so that Access gets confused and returns say no records
> when it should get 2. But not all the time. Just sometimes - it was
working
> 100% ok today for example. The use of DBDateTimeFormat was meant to
overcome
> that locale problem, but apparently there is more going on.

> Any suggestions on how to sort this problem? Any help appreciated.

> Thanks,

> Lauchlan M.

Re:Problem with Access Date-Times in non-US locale


Jet functions are similar to VB Script functions. You can use also
DateSerial(year, month, day). This should work or any local and not depend
on user local.

Regards,
Vassiliev V.V.
http://www.oledbdirect.com

"Lauchlan M" <LMackin...@Hotmail.com> ???Y/???Y ????? ???Y??:
news:3d58e5e0@dnews...

Quote
> Thanks Vassiliev.

> Do you know why Access might be flaky on this issue - work sometimes but
not
> others? It seems to be intermittent about working or not when I took steps
> that seem to be pretty similar to what you describe.

> Regards,

> Lauchlan Mackinnon.

Re:Problem with Access Date-Times in non-US locale


Been down this long road.... So now my solution is never to use Access date
type, simply define the table column as a double. Then I know what is
happening, until the no-US locale effects on decimal separator bit me!!

Dave

Re:Problem with Access Date-Times in non-US locale


Quote
"Dave Blake" <barnsw...@hotmail.com> wrote in message news:3d5a13f3@dnews...
> Been down this long road.... So now my solution is never to use Access
date
> type, simply define the table column as a double. Then I know what is
> happening, until the no-US locale effects on decimal separator bit me!!

How did you deal with that?

Avoiding the Access datetime field type seems like a good idea at this
point!

Lauchlan M.

Re:Problem with Access Date-Times in non-US locale


Quote
"Lauchlan M" <LMackin...@Hotmail.com> wrote in message

news:3d5a345f@dnews...

Quote

> > Been down this long road.... So now my solution is never to use Access
> date
> > type, simply define the table column as a double. Then I know what is
> > happening, until the no-US locale effects on decimal separator bit me!!

> How did you deal with that?

See previous thread about regional settings. I forced date separator to '.'
for my entire app, but there are alternatives because it only "bites" in
dymanic SQL strings.

Quote

> Avoiding the Access datetime field type seems like a good idea at this
> point!

Yes, I decided that back in my (brief) VB days... many years ago :))

Other Threads