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

Tim Johnson wrote:

> 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.

[Chris Hohmann]

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

> 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.

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

>-----Original Message-----
>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
>
>Tim Johnson wrote:

>> 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.

>
>
>
>.
>

[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

[Chris Hohmann]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:uOTZERVQDHA.1624@tk2msftngp13.phx.gbl...

> Aaron Bertrand [MVP] wrote:

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

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

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.

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

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".
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/pg_ado_providers_6.asp[/url]

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