Problems with inserting european dates with sql sentences

  • Thread starter Thread starter hagi
  • Start date Start date
H

hagi

when I wrote the following sql sentence
INSERT INTO dates_table ( HolidayDate )
VALUES (#01/02/1998#);
the date 02/01/1998 was stored in the table.

but when I wrote this sql sentence
INSERT INTO dates_table ( HolidayDate )
VALUES (#15/02/1998#);
the date was entered correctly (15/02/1998)

how do I avoid this problem?
Hagi
 
Hi,


A date entered between # is FIRST interpreted in the US format.
#01/02/1998# is thus the second of January. #15/02/1998# cannot be a US
format, but rather than throwing an error, OLEAUT32.dll (it is used by
Access, but also by Excel, and Word, and ... ) will try other variations,
and end up with the 15th of February of 1998.

When a data is displayed for the end user, it is displayed accordingly
to the local setting. In Finland, you probably end up with 1998.01.02
and 1998.02.15 Your regional setting seems to be dd/mm/yyyy.

What to do? nothing if you are in a from, Access is aware an EXPECT you
enter date, without #, accordingly to your regional setting. BUT if you are
a developer, then you have to FORCE a US format:


strSQL="INSERT INTO ... VALUES(" & _
Format( theDate, "\#mm/dd/yyyy\#" ) & " ); "


otherwise, if you use just &, the conversion from Date to String will be
done accordingly to your regional setting (as if the date would have to be
printed on a form, or on paper, for the end user to be eventually seen). It
is assume the data, the string, is routed to the end user... while HERE, it
is routed to the database engine... and the database engine is a user who
expect to see the date, delimited in #, in a US format. Just a matter to
know our "client", the end user, or the database... :-)


Hoping it may help,
Vanderghast, Access MVP
 
Michel Walsh said:
If you are a developer, then you have to FORCE a US format:

strSQL="INSERT INTO ... VALUES(" & _
Format( theDate, "\#mm/dd/yyyy\#" ) & " ); "
Click to expand...

There is an alternative: use an unambiguous date e.g.

Format$(theDate, "\#dd mmm yyyy\#")

Jamie.

--
 
Hi,


That won't work, highly probably, in countries not using the same month
names (Spanish, French, Portuguese, German, ... assuming you format on a PC
where English is the default language setting)



Vanderghast, Access MVP
 
That won't work, highly probably, in countries not using the same month
names (Spanish, French, Portuguese, German, ... assuming you format on a PC
where English is the default language setting)
Click to expand...

Are you suggesting the OP may be formatting an INSERT INTO statement
in one country and executing it in another? Unlikely, to say the
least!

Otherwise, dd mmm yyyy will always give an unambiguous local date.

Jamie.

--
 
Hi,


Indeed, for a not-split application, and even in split application, with
Jet, the command is applied locally, on the client, not on the server, so
the problem should not occur.


Vanderghast, Access MVP
 
Michel Walsh said:
Indeed, for a not-split application, and even in split application, with
Jet, the command is applied locally, on the client, not on the server, so
the problem should not occur.
Click to expand...

Good point.

Thanks,
Jamie.

--
 
Back
Top