Date format in update query

[John]

Hi

How can I ensure that date is always assigned in dd/mm/yyyy format to a date
filed, in an update query?

Thanks

Regards

 

[Allen Browne]

John, Access does not store dates in a specific format. The format is
applied at display time, and depends on the user's Regional Settings (in the
Windows Control Panel.)

So perhaps your question is, "How can I ensure that Access understands the
dates in the query, and stores the correctly when the Update query
executes?"

I also live in a d/m/y country, and in 15 years of working with Access have
found 3 cases where Access is likely to misunderstand our dates. Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If the dates are calculated ones, typecasting them in the query can also
help:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[jacksonmacd]

Allen

I found your ser-36 article to be extremely useful. I written my own
SQLDate() wrapper function, but apparently mine contained two errors
that your function does not. I am baffled why the function appears to
have worked properly for the (at least) three years that I've been
using it. Can you offer any insight why it continued to work with
these two errors? Was I just lucky...??

1) I did not know about the "\/" format trick, and my output used the
"-" that you mention
2) My output uses "yyyy/mm/dd" format, and as far as I can tell, it
always selects the correct data.

On tweak that my function includes is an optional boolean parameter
whether or not to include the time portion of the date in its output.
With a default value of False, the normal calls to the function are
identical to yours. Simply adding "TRUE" after the passed value causes
the time to be included in the formatted output string. I find it
handy in many situations.

 

[Allen Browne]

That's good. Hopefully you will get consistent results.

Re the yyyy/mm/dd format, although that is not the native JET format, I
would expect it to be interpreted consistently, unless the mm part is
greater than 12. That is an international database standard, and it seems to
me like the most logical format to use, and those who use it generally
report that it gives consistent results. The reason I recommend the
mm/dd/yyyy format is that this is what Access/JET does natively.

I like the idea of an optional boolean argument for specifying whether to
include the time component. Not only is it flexible, but acts as a memory
jogger for any developer who uses the function. I might incorporate that
idea too.