SQL returns date out of range

OK ... here is the deal...in another section I couldn't get the answer
VBscript for ActiveServerPages connecting to MSSQL7

TwoDayX = Date()           is 8/29/2003
TwoDayX = Date() +30    is 9/29/2003
OK this part works...VBscript for ASP pages   that works, always has...ok next



the date coming back from the database (MSSQL7) is 8/3/2003 (from my query)
IT IS A DateTime type. i have even tried the smalldate...

WHAT THE PROBLEM IS:
There is no 0 (zero) in front of the day (3) so the code thinks that the day(3) begins after day(20,21,22,23---29)

I cannot get the convert to...  (Convert(Datetime, EventDate, 101) --- to--- 08/03/2003

8/3/2003 BAD
08/03/2003 GOOD


Here is the Real SQL statement I am using:

cmdTempS2.CommandText = "SELECT * FROM CalendarOfEvents WHERE(Category LIKE '%" & SchTxt & "%') OR (EventTitle LIKE '%" & SchTxt & "%') OR (EventDescription LIKE '%" & SchTxt & "%') AND (Convert(Datetime, EventDate, 101)  BETWEEN '" & TwoDayX & "' AND '" & TwoDayXa & "') AND (ShowEvent = 1) ORDER BY EventDate"



ok lets forget about my TwoDayX variable. it works fine, always has. It is the DB date that is giving me fits!

The date coming from DB is what needs converting.
The DB has the date 8/3/2003   I need it to be 08/03/2003.
If I type in 08/03/2003 ... the DB changes it to 8/3/2003

because it is getting past the WHERE clause

8/3/2003 is not greater than 8/29/2003 ... nor does it fall in between the date range of 8/29/2003 - 9/29/2003

I need zeros in front of the single digits. The database needs to place the zeros so that the query will filter out the earlier date of 8/3/2003.

It is acting like a string...computers screw up the ordering of dates if they are thought to be strings (understandable).

Is the way I am splitting the query doing it?
example:  (extra spaces are for clarity)
select * From MyTable WHERE(EventDate > ' " & WhateverX & " ')

I used to use the # with dates...but SQL7 won't let me use #
#" & MyVariableDate & "#

so the only thing that brings back any data is the ' " & MyVariableDate & " '

everything else errors



Make a table called TESTER with a column called EVENTDATE ..datetime as type
put the date 08/03/2003 into the DB column EVENTDATE
(did the DB change the date to 8/3/2003 ?????) (mine did)

MAKE AN ActiveServerPage call it whatever.asp

Here are the 2 dates to check against: this works
<%TwoDayX = Date() %>                     is-------- 8/29/2003
<%TwoDayXa = Date()+30 %>             is-------- 9/29/2003

Write SQL statement:
"SELECT * FROM TESTER WHERE(EVENTDATE BETWEEN '" & TwoDayX & "' AND '" & TwoDayXa & "')"


See what results you get!
8/3/2003   IS NOT in the date range...but it shows up any way.

HELP ME PLEASE!