ASP MS Access Date in Where clause returns unexpected values

[gsamm]

I am building a web site in ASP using MS Access as the database (for the time being)

I excecute the following SQL query to retrieve records that are still 'open' (e.g the CloseDate must be in the future)

SQL = "SELECT * FROM Project Where CloseDate > #" & Date() & "#;"

This statement returns all the records regardless of the date!

The date in MS Access is defines as a short date (dd/mm/yyyy)
and 'date()' returns todays date in dd/mm/yyyy format

the LCID is set correctly (2057 for UK)

I am having a real head scratch over this.....

 

[DotNetGnat]

Try this:

SQL = "SELECT * FROM Project Where CloseDate > "# & Now() & #";"

-DNG

 

[gsamm]

Hmmm, I have just ried that and it still retrieve everything, regardless of the date.

The code now reads:
SQL = "SELECT * FROM Project Where CloseDate > #"& Now() &"#;"

 

[Mighty]

Firstly, when you run this command from ASP, you don't need the ";" at the end. Have you checked the intrgrity of your data to make sure that all the data in the DB is as you expected.

Also have you printed the sql command to the screen to make sure it's correct.

Mighty

 

[DotNetGnat]

Please Response.Write your sql string and post the output here...

Thanks

-DNG

 

[gsamm]

OK,

This is the code that I am now using to check:

SQL = "SELECT * FROM Project Where CloseDate > #"& Now() &"#;"
response.Write("date = ")
response.Write(date)
response.Write("<br>")
response.Write("SQL = ")
response.Write(SQL)

The output I get is:

date = 02/06/2005
SQL = SELECT * FROM Project Where CloseDate > #02/06/2005 15:13:34#;

I also took the precaution if including the following to check the actuall contents of the records:

response.Write("CloseDate = ")
response.Write(RS_Project00.Fields.Item("CloseDate").Value)

The output I get is:

CloseDate = 29/05/2005

Something is very wrong here, probably quite a simple 'wrong' but nonetheless very frustrating.

 

[gsamm]

Just as an aside, I am using the driver:

Connection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/cands/database/project.mdb")

I have also tested it with the ODBC driver and it produces the same result.

 

[DotNetGnat]

Try this:

SQL = "SELECT * FROM Project Where CONVERT(varchar(20),CloseDate,109) > #"& Now() &"#;"

-DNG

 

[gsamm]

Hmmm, It looks like Access doesn't support the convert function:

Microsoft JET Database Engine (0x80040E14)
Undefined function 'CONVERT' in expression.

It looks as though it is interpreting the 'dd' part of the date first and obviously thinks that '29' is greater than '02'.

OK but that still doesn't explain why it is disregarding the fact that it is a date field and not comparing the 'mm' and 'yyyy' parts as well.

 

[DotNetGnat]

How about this:

SQL = "SELECT * FROM Project Where DateValue(CloseDate) > #"& DateValue(Date()) &"#;"

Also try with Now() function...

-DNG

 

[gsamm]

Haha - nothing

the code:
SQL = "SELECT * FROM Project Where DateValue(CloseDate) > #"& DateValue(Date()) &"#;"
response.Write("date = ")
response.Write(date)
response.Write("<br>")
response.Write("SQL = ")
response.Write(SQL)

the output:
SQL = SELECT * FROM Project Where DateValue(CloseDate) > #02/06/2005#;

the code:
response.Write("CloseDate = ")
response.Write(RS_Project00.Fields.Item("CloseDate").Value)

the output:
CloseDate = 29/05/2005

Humph! I'm going to get a coffee and a large brandy.

 

[DotNetGnat]

Shouldnt that be :

SQL = "SELECT * FROM Project Where DateValue(RS_Project00.Fields.Item("CloseDate").Value) > #"& DateValue(Date()) &"#;"


-DNG

 

[gsamm]

DNG,
No mate,

the code looks like this:

SQL = "SELECT * FROM Project Where DateValue(CloseDate) > #"& DateValue(Date()) &"#;"
response.Write("date = ")
response.Write(date)
response.Write("<br>")
response.Write("SQL = ")
response.Write(SQL)
Set RS_Project00 = objConn01.Execute(SQL)
response.Write("CloseDate = ")
response.Write(RS_Project00.Fields.Item("CloseDate").Value)

As you can see there is no way you can use the RS_Project00 value in the SELECT because that is the value we are trying to retrieve. 'CloseDate' is a datefield from the 'Project' table

 

[DotNetGnat]

I think i am confused

Did you try this one too:

SQL = "SELECT * FROM Project Where CloseDate > #"& DateValue(Date()) &"#;"

-DNG

 

[gsamm]

Yep, I'm afraid I've already tried it. I will double check though just in case...

...nope, still fails to weed out those future dates.

There is something very screwy going on here. I just can't understand why the dates appear to be being compared as strings rather than dates.

 

[gsamm]

STOP PRESS!

I know why it is not working:

Although the date is stored in access as dd/mm/yyyy it is still being passed back to the ASP page as mm/dd/yyyy.

I found this out by hard coding the date into the SQl string:

the code:
SQL = "SELECT * FROM Project Where CloseDate > #06/06/2005#;"

the result:
Same as ever

the code:
SQL = "SELECT * FROM Project Where CloseDate > #07/06/2005#;"

the result:
no records returned!!!

I'm now doing further investigation...

 

[arst06d]

I hate dates
I always try to store dates as yyyymmdd format wherever can - it makes life so much simpler.

 

[gsamm]

OK, here is the answer!

the code:
v_comparedate = (Month(Date))&"/"&(Day(Date))&"/"&(Year(Date))
SQL = "SELECT * FROM Project Where CloseDate > #" & v_comparedate & "#;"

The result:
it works!

Therefor:
Everything is stored correctly in access in dd/mm/yyyy format.
When you use response.write(Date) the format is also dd/mm/yyyy (it picks up the LCID setting)

BUT when you are doing a date comparison the LCID date formatting is apparently ignored because the code is comparing the access dd/mm/yyyy to a date mm/dd/yyyy

I can't believe that. All my 'response.write' statements which I used to check the data looked OK. Just goes to show - never trust the internal code...

 

[gsamm]

DNG,
Thanks for the help. Bouncing the code around for a while was really useful. It sometimes helps just to get someone to confirm that you are not doing anything really silly.

 

[DotNetGnat]

No Problem...Glad that its working now..

-DNG