Board index » delphi » Interesting feature

Interesting feature


2004-12-06 04:38:15 AM
delphi53
D7, Access 97 tables, ADO
I've just spent a long time on this, and I am unsure if I have solved it
properly.
I'm in the UK, so dates are dd/MM/yyyy and the first line of my code
executed is:
shortdateformat := 'dd/MM/yyyy';
(though this makes no difference to the problem described below)
I've got two tables, related/joined by the date.
It works well if the date is unequivocally UK, eg 31/11/2004
If the date is ambiguous UK/US my SQL:
select * from activity table where activitydate = #11/10/2004#
(which forms the commandtext of a TBetterADODataSet)
is read not as 11th Oct, but as 10th Nov, and so it doesn't work, it fails
to find the records from 11th Oct.
It took AGES to figure this out.
activitydate is filled in in the onnewrecord event as activitydate.value :=
daystabledate.value, it ain't me assembling it from something.
My fix (cludge, I fear) is to use decodedate to get years, days, months and
to assemble a mm/DD/yyyy date into the command text.
This WORKS, but I am afraid it isn't robust, for example if a user has funny
settings on their machine.
Comments? Thanks
Andrew
 
 

Re:Interesting feature

ps I couldn't think of a snappy enough description of this for Google!
AS
 

Re:Interesting feature

"Andrew Skinner" <XXXX@XXXXX.COM>wrote
Quote
ps I couldn't think of a snappy enough description of this for Google!
What about
www.google.com/search
--JohnH
 

Re:Interesting feature

Hi, Andrew.
We just use, in Brasil, the format dd/mm/yyyy. Than, this subject also
worried me.
At that time, I created a table with records to day 5 of March and day 3 of
May. I concluded that the format used for ADO and DAO engine is always the
American: mm/dd/yyyy. Than, I have used, without problems, the function
FormatDateTime('"#"mm/dd/yyyy"#"', varDate) to perform SQLs expressions.
"Andrew Skinner" <XXXX@XXXXX.COM>escreveu
na mensagem news:XXXX@XXXXX.COM...
Quote
D7, Access 97 tables, ADO

I've just spent a long time on this, and I am unsure if I have solved it
properly.

I'm in the UK, so dates are dd/MM/yyyy and the first line of my code
executed is:

shortdateformat := 'dd/MM/yyyy';

(though this makes no difference to the problem described below)

I've got two tables, related/joined by the date.

It works well if the date is unequivocally UK, eg 31/11/2004

If the date is ambiguous UK/US my SQL:

select * from activity table where activitydate = #11/10/2004#

(which forms the commandtext of a TBetterADODataSet)

is read not as 11th Oct, but as 10th Nov, and so it doesn't work, it fails
to find the records from 11th Oct.

It took AGES to figure this out.

activitydate is filled in in the onnewrecord event as activitydate.value
:=
daystabledate.value, it ain't me assembling it from something.

My fix (cludge, I fear) is to use decodedate to get years, days, months
and
to assemble a mm/DD/yyyy date into the command text.

This WORKS, but I am afraid it isn't robust, for example if a user has
funny
settings on their machine.

Comments? Thanks

Andrew



 

Re:Interesting feature

Ho Ho!
Thanks, but the early hits don't address my fear that the *solution* might
not be robust.
Nor do they explain why ADO doesn't {*word*88} on dates like 30/11/2004, which
would have given the game away and saved me about 12 hours of work. I
suppose this is M$'s idea of being helpful.
Andrew
 

Re:Interesting feature

"Andrew Skinner" <XXXX@XXXXX.COM>wrote
Quote
Nor do they explain why ADO doesn't {*word*88} on dates
like 30/11/2004,
In my work as a "programmer analyst", I have met
programmers who would think that they were doing
you a favor by getting the program to do the right
conversion half the time rather than throwing an error,
which would cause them to have do some extra work
to handle the error. --JohnH
 

Re:Interesting feature

"Andrew Skinner" <XXXX@XXXXX.COM>wrote in
message news:XXXX@XXXXX.COM...
Quote
D7, Access 97 tables, ADO

I've just spent a long time on this, and I am unsure if I have solved it
properly.

I'm in the UK, so dates are dd/MM/yyyy and the first line of my code
executed is:

shortdateformat := 'dd/MM/yyyy';

<snip>
Quote
is read not as 11th Oct, but as 10th Nov, and so it doesn't work, it fails
to find the records from 11th Oct.

It took AGES to figure this out.

activitydate is filled in in the onnewrecord event as activitydate.value
:= daystabledate.value, it ain't me assembling it from something.

My fix (cludge, I fear) is to use decodedate to get years, days, months
and to assemble a mm/DD/yyyy date into the command text.

ADO uses the American format for dates as a default. I have had consistent
success by putting the date in the form: #dd mmm yyyy# as this cannot be
ambiguous and works in all countries.
HTH
Andrew
 

Re:Interesting feature

Thanks, I would not thought of that, and probably never would have done.
Andrew
 

Re:Interesting feature

Proper decision for this problem would be to use parameter instead of date
time constant. Something like that:
qr.sql.text := 'select * from activity table where activitydate =
:activitydate';
qr.Parameters.ParamByName('activitydate').Value := ADateTimeValue;