International Date

[Guest]

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?

 

[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

 

[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?

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

 

[Guest]

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

 

[Alex Dybenko]

to be rocognized correctly - date should be in # and in mm/dd/yyyy format:

?DatePart("d",#02/12/2005#)
12

--
Best regards,
___________
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

 

[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)

 

[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

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