date format

  • Thread starter Thread starter mike H
  • Start date Start date
M

mike H

hello.
how to insert/update a date in dd-mm-yyyy format?
for example:

UPDATE supplierTable SET DateEntered = #31-12-2003# ...

if i'm using #31-12-2003# it will be stored properly,
(31st december 2003), but if i'm using #04-01-2004# it
will recognised the 04 as the month (1st April 2004).

thanks,

mike
 
You can't. Access expects the dates to be in mm/dd/yyyy format in SQL
statements, regardless of what the short date format has been set to on the
workstation. (Okay, so that isn't exactly true: it will accept any
unambiguous format, such as dd-mmm-yyyy or yyyy-mm-dd. The point is, if you
give it a date in dd/mm/yyyy format with dd less than or equal to 12, it's
going to treat it as mm/dd/yyyy, as you've already found.)

However, this shouldn't be a problem, as dates aren't stored with any format
(they're 8 byte floating point numbers, where the integer part represents
the date as the number of days relative to 30 Dec, 1899, and the decimal
part represents the time as a fraction of a day)

If your date value is actually in a variable, you can use the Format
function to make sure it's in the correct format in your SQL statement. My
favourite approach is to use what's in
http://www.mvps.org/access/datetime/date0005.htm at "The Access Web".

Another option is to use the CDate function: it respects the workstation's
short date format. However, recognize that you can't use the # delimiters
then. Your statement would need to be something like:

UPDATE supplierTable SET DateEntered = CDate('04-01-2003') ...


You may find Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html to be useful reading.
 
JET expects the format "#mm/dd/yyyy#"

Try:

UPDATE supplierTable SET DateEntered = #12/31/2003#
 
hello.
how to insert/update a date in dd-mm-yyyy format?
Click to expand...

You can't.
for example:

UPDATE supplierTable SET DateEntered = #31-12-2003# ...

if i'm using #31-12-2003# it will be stored properly,
(31st december 2003), but if i'm using #04-01-2004# it
will recognised the 04 as the month (1st April 2004).
Click to expand...

The programmers who designed Access live in the United States, and use
the United States mm/dd/yyyy format. They had to decide how date
literals would be interpreted and chose that format. Any format with
two digits, two digits, and two or four digits in a date literal will
be interpreted as month-day-year.

You can convert your local date format to the required mm/dd format
using an expression like

UPDATE supplierTable SET DateEntered = Format(CDate("31-12-2003"),
"mm/dd/yyyy")

or you can use an unambiguous format such as

#31-Dec-2003#

but you CANNOT force Access to treat 03/04/2004 as anything other than
March 4th.
 
Back
Top