SQL query with dates

[CortoMaltes]

I looked in the FAQs and didn't find the help I need so I hope that any of you may help me with this (ASP/MS Access/SQL query).

I have a page where users may display all future events. To do so I use the SQL:

queF = "SELECT * FROM Events WHERE (Start_Date BETWEEN " & EDate & " AND " & DateVal & " OR End_Date BETWEEN " & EDate & " AND " & DateVal & &quot " & PresentAdd & ""

EDate = current date
DateVal = last day of year
PresentAdd = session SQL condition (e.g. "AND Status='member') to condition the information displayed to public users or members only

The query runs fine both in Access and in the ASP on IIS 5 (my PC). However, it doesn't return results when run on IIS 4 (the server where the site runs). I have thought of date format issues (my dates are stored in the format dd/mm/yyyy (short format); my PC uses this format; I am trying to check which are the regional date settings on the server. I am almost sure that this must be the problem...) but all the information I find about date querying points to identical solutions:
- Do not store dates as Date field (i.e. use Text fields to store the date in the format desired)
- When querying date fields, manipulate the date to match the database format

My question is: Is there any way I can define the database field as Date, use the format that I want (dd/mm/yyyy) and not worry about the server's regional settings? What would be the least cumbersome way to handle date querying and make sure the query works, independent of the platform? Thank sin advance for helpful hints

 

[sweevo]

Putting date values between # in your sql tells the DB it is dealing with a date. As far as I know, the DB will store it in the format its field has been defined to, regardless of the format you send to it (as long as it's a valid date)

 

[CortoMaltes]

sweevo, the dates sent with the SQL are defined like e.g:

EDate = "#" & date & "#"
DateVal = "#01/01/03#"

As detailed, the query runs fine in Access query window. It also runs fine in the IIS 5 machine. The problem is that it doesn't produce any results on the production server (IIS 4)

 

[Dynapen]

Is it safe to assume that all the rest of your ASP pages work fine on the IIS 4.0 box?

The IIS on the machine shouldn't affect the date command, becuase it is the database that is processing the command line, and not the IIS.

I would check the settings on your IIS 4.0 box, and see is there are any configuration differences between the 4 and 5 boxes. The money's gone, the brain is shot.....but the liquor we still got.

 

[CortoMaltes]

Well, as I suspected it is directly related to the NT machine (the one running IIS 4) regional settings that set the date format as mm/dd/yyyy. The only way around is to format the date submitted as

EDate = "#" & Day(date) & "/" & Month(date) & "/" & Year(date) & "#"

and the script runs fine. Indeed it has nothing to do with versions but the OS date format...