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