ASP & MS Access and Dates...

[Tim Johnson]

Hi,

I am fairly new to ASP so please forgive any possible
stupidity. I have a series of ASP pages that read/write
to an Access database. One of the pages queries a table
using an SQL statement - "SELECT * FROM TABLE WHERE
DATEFIELD = #2/07/2003#" this is the query that I am
using. In the access DB the DateField is setup as
a "Date/Time" datatype and I have set the format
to "Short Date", the format of the short date
is "DD/MM/YYYY". When I view the table within Access all
the DateField fields are displayed in the correct
DD/MM/YYYY format, I have about 10 records which have
2/07/2003 in the DateField field. However, when I query
the table using the above SQL statement I don't get any
rows returned? However if I changed the SQLquery
to "SELECT * FROM TABLE WHERE DATEFIELD = #07/2/2003#" I
would get 10 rows returned. Why is this? I would have
assumed that if in the Access DB the datatype format is
set to dd/mm/yyyy then in the query it would also expect
dd/mm/yyyy - HELP!!!

In my regional settings I have set the short date to be
dd/mm/yyyy also.

Please help. TIA.

[Bob Barrows]

In Access, the Format property effects only the manner in which the date is
displayed, not how it is stored. Jet stores dates a a Double number, with
the whole number portion containing the number of days since the seed date,
and the decimal containing the fraction of the day (.5 = noon). Jet knows
nothing about the Access-specific Format property. Here's an excerpt from
Access online help from the JetSQL reference:
When you specify the criteria argument, date literals must be in U.S.
format, even if you are not using the U.S. version of the Microsoft® Jet
database engine. For example, May 10, 1996, is written 10/5/96 in the United
Kingdom and 5/10/96 in the United States. Be sure to enclose your date
literals with the number sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must
use the following SQL statement:

SELECT *

FROM Orders

WHERE ShippedDate = #5/10/96#;

HTH,
Bob Barrows

[Chris Hohmann]

"Tim Johnson" <ti...@smithscity.co.nz> wrote in message
news:05e401c340e6$babefb80$a101...@phx.gbl...

The preferred way to handle dates so this type of confusion is
eliminated is to use the ISO 8601 (YYYY-MM-DD). There no ambiguity in
what that dates represents. As opposed to 7/2/03 which could stand for
July 2nd, February 7th 1803,1903,2003,2103... or any combination there
of. Any database worth its salt accepts ISO 8601 dates including Access,
SQL Sever, Oracle, MySQL, etc... What's not to love. Portability, no
ambiguity, sorts correctly even when text, fixed width for legibility...
Once you go ISO, you'll never look back. Your query would look like
this:

SELECT * FROM TABLE WHERE DATEFIELD = '2003-07-02'

I like to use apostrophe as the date delimiter because it's more
portable if you later decide to migrate to SQLServer, Oracle, etc...
Also consider explicitly referencing the fields you want to return
instead of using *.

HTH
-Chris

[TIm Johnson]

Thanks guys, both answers my question. That has cleared
up in my head why it was doing such weird things, thanks
alot :)

>.
>

[Aaron Bertrand [MVP]]

> The preferred way to handle dates so this type of confusion is
> eliminated is to use the ISO 8601 (YYYY-MM-DD).

A better habit is YYYYMMDD, especially if you ever plan on moving to SQL
Server. Try this:

SET DATEFORMAT MDY
SELECT ISDATE('2003-01-13')
SELECT ISDATE('20030113')

SET DATEFORMAT DMY
SELECT ISDATE('2003-01-13')
SELECT ISDATE('20030113')

So as you can see, relying on YYYY-MM-DD format can bite you later if the
dateformat is set to DMY or the language set to British, & c.

A

[Bob Barrows]

For future reference, this format (YYYYMMDD) does not seem to be supported
in JetSQL. This test generates a "Syntax error in date in query expression
'eventdate=#20030415#'" error:

Dim cn, rs
Set cn = server.createobject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx"
Set rs = cn.Execute("select * from tbltable where eventdate=#20030415#", _
, adCmdText)
rs.Close
Set rs = Nothing
cn.close
Set cn = Nothing

while this one works fine:
Dim cn, rs
Set cn = server.createobject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx"
Set rs = cn.Execute("select * from tbltable where eventdate=#2003-04-15#", _
, adCmdText)
rs.Close
Set rs = Nothing

cn.close
Set cn = Nothing

I get the same results using the Access Query Builder.

Bob

[Chris Hohmann]

"Bob Barrows" <reb_...@yahoo.com> wrote in message
news:uOTZERVQ...@tk2msftngp13.phx.gbl...

While both YYYYMMDD(basic) and YYYY-MM-DD(extended) are acceptable
representations of dates under the ISO 8601 standard, I prefer the
extended format for a number of reasons:
1. Basic is not recognized by the Jet as Bob pointed out.
2. Basic fails (runtime error) and/or is not recognizes as a date in
VBScript:

Response.Write IsDate("2003-07-01")
Response.Write IsDate(#2003-07-01#)
Response.Write IsDate("20030701")
'Response.Write IsDate(#20030701#)

3. There is some ambiguity in basic in that 20030703 can be interpreted
as the number twenty million thirty thousand seven hundred and three or
as the date July 3rd, 2003
4. The extended format is the one adopted by the W3C in its XML Schema
Part 2: Datatypes.

Here's a submission to the W3C that does a nice job of outlining a
proposed profile(subset) of the ISO 8601 specification for use in W3C
standards. As noted prominently at the top of the article, this proposal
is in no way endorsed by the W3C.

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

With regards to SQL Server, there are some alternatives to using
Microsoft's language independent date format.
1. Select us_english as the default language. While some (i.e.
Canadians, ay!) may consider this a US centric view one could argue that
us_english is the default "default language" in SQL Server. So much so
that us_english is not included in the syslanguages table, it's just
always available regardless of which language-specific version of SQL
Server you're running.
2. Use "SET DATEFORMAT YMD" in TransactSQL which deals with dates.
3. Specify an ISO 8601 "friendly" language as the provider specific
connection parameter "Current Language".
http://msdn.microsoft.com/library/en-us/ado270/htm/pg_ado_providers_6.asp

I'd really like to see Microsoft adopt the extended ISO 8601 format as
their language independent date format, especially if they intend to
support XML/XMLSchema natively in the database engine for the reason
identified in item 4.

As always, my opinions are my own. The above is just food for thought.

<response tone="joking">
While we're on the topic of food... you guys aren't fooling anyone. It's
not Canadian bacon, it's ham! :-p
</response>

-Chris

[Aaron Bertrand [MVP]]

> 1. Select us_english as the default language. While some (i.e.
> Canadians, ay!)

That's "Canadians, eh?"

> While we're on the topic of food... you guys aren't fooling anyone. It's
> not Canadian bacon, it's ham! :-p

Then you've never had it!