International Date

N

nick

Hi there, I want to be able to display dates in an international format, ie
11/10/2003 becomes 10-Nov-2003. Is there a built-in function for this or am I
going to have to go ahead and write one?
 
A

Allen Browne

Internally, Access stores date/time values are numbers. The whole number
represents the date, and the fractional part the time (e.g. noon = .5, 6am =
..25, and so on).

That means the display format is independent of how the data is stored. By
default, it displays in the interface according to your settings in the
Windows Control Panel under Regional Options. If you want to force a
different display, use the Format() function.

When working with SQL statements or clauses, or literal dates in VBA code,
the story is different. More info in:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html
 
J

John Vinson

Hi there, I want to be able to display dates in an international format, ie
11/10/2003 becomes 10-Nov-2003. Is there a built-in function for this or am I
going to have to go ahead and write one?
Click to expand...

Any date is stored internally as a Double Float number, a count of
days and fractions of a day (times) since midnight, December 30, 1899.
You can display dates however you wish.

For the format you suggest, change the Format property of the field in
table design view - and/or the Format property of the textbox in which
you display the field - to dd-mmm-yyyy.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
N

nick

You beautiful people. Problem solved. Thankyou.

I was busy writing my own. Although this had me stumped
?DatePart("d",2/12/2005)
30
?DatePart("d",1/1/2000)
30

Mayhap you need to wrap date in quotes? I dunno.

cheers
 
D

david epsom dot com dot au

That's a European format. Although most of the (Gregorian)
world uses the European format, most people who pay attention
would consider 'international' format to be year month day
(like Japan: yy/mm/dd). ISO8601 is yyyy-mm-dd (ODBC canonical
format is the same). See
Info on ISO 8601, the date and time representation standard
http://www.cs.tut.fi/~jkorpela/iso8601.html

Access canonical format is numeric, with the same values as VB.
Today's date is 38327. It's fast, unambiguous, and efficient.
Unfortunately it is not really compatible with ODBC/C/SQL Server,
and not self-evident in any language.

Given the compatibility problem, in Access SQL the best format
is the ISO8601/ODBC canonical format. Access doesn't do 'date
guessing' on dates expressed in that format: #2004/20/12# will
give you an error, unlike #20/12/2004# and #12/20/2004#.

(david)
 
J

John Vinson

You beautiful people. Problem solved. Thankyou.

I was busy writing my own. Although this had me stumped
?DatePart("d",2/12/2005)
30
?DatePart("d",1/1/2000)
30
Click to expand...

Wrap the date in # marks, as Alex suggests. Otherwise it will assume
you're doing a division operation - dividing 2 by 12, and then by
2005. This will get a rather small number (8.31255195344e-5) which
corresponds to about 7 seconds after midnight, December 30, 1899. So
the "d" part of that date is... 30!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top