[thelist] Re: Access: Recording time

rudy rudy937 at rogers.com
Tue Jul 15 06:20:46 CDT 2003


> ... when I try to assign the value "7/1/2003" it ends up being
> "12:05:01 AM".

and

> So I entered "2003-07-01", and Access seems to have converted it to
> "6/17/1905"

easy enough to explain

did you use the octothorps i mentioned?

without them, if you enter 7/1/2003, that's 7 divided by 1 divided by 2003,
which comes out to a number quite close to zero, hence the "zero date" (not
sure where the 12:05 time comes from, but perhaps that's a result of the
fraction)

if you enter 2003-07-01, that's 2003 minus 7 minus 1, which is 1995, which
(if you divide by 365) is about 5 and a half years, which, if you start at
1900, puts you into june in 1905

see, date literals have to be delimited, otherwise the database has no
choice but to interpret them as arithmetic expressions (and access is too
clever by half, it takes a number and converts it to a date by assuming it's
a number of days)

in the sql standard, the delimiters are single quotes, but in access,
octothorps

    #2003-07-16#

and no matter what database you're working with, but especially in access,
you will avoid ambiguities between year/month and month/year simply by
using ISO format


rudy



More information about the thelist mailing list