Date Format

[Guest]

Hi ther
I am selecting a date field from an access table taking the date from a asp file but it will not work if I put in the date in UK format dd/mm/yyyy but will work using US format mm/dd/yyyy I want to be to use UK forma
Thanks

 

[Douglas J. Steele]

Sorry, but Access doesn't recognize non US formats in SQL queries (or, to be
more precise, Access doesn't recognize ambiguous date formats: it will
recognize dd mmm yyyy)

Fortunately, this shouldn't matter to you. Dates aren't stored with any
format: they're stored as 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. All you need to
do is ensure that your dates are in a format Access will recognize in your
queries, and everything will be fine.

Take a look at Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or at the September
2003 Access Answers column I have at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi there
I am selecting a date field from an access table taking the date from a

asp file but it will not work if I put in the date in UK format dd/mm/yyyy
but will work using US format mm/dd/yyyy I want to be to use UK format

 

[John Vinson]

Hi there
I am selecting a date field from an access table taking the date from a asp file but it will not work if I put in the date in UK format dd/mm/yyyy but will work using US format mm/dd/yyyy I want to be to use UK format
Thanks

An Access Date/Time value is *stored* as a Double Float number, a
count of days and fractions of a day since midnight, December 30,
1899. The format just controls how it's displayed.

However, an Insert statement - actually any literal date in SQL - must
be in either American mm/dd/yyyy format or an unambiguous format such
as 23-Mar-2004.

To insert UK dates in a SQL statement, use an expression like

strDate = "23/03/2004"
Format(Cdate(strDate), "mm/dd/yyyy")

This will take the date provided by the user, convert it to a
Date/Time using the user's regional settings, and cast it into
American format for loading into the table.