SQL returns date out of range

VolfrikSemisari used Ask the Experts™
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...

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:

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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

You have to convert to varchar type ...

Convert(varchar(10), EventDate, 101)  



after 14 hours I finally figured out on my own!

I don't know how to remove the question so anyone who gets the solution gets the 500 points.

hint: don't think so technical!

The solution is in the other question I asked ... look hard at it.


You aren't going to believe this!

it has nothing to do with CONVERT or my variables.

I don't know how to undo the question so now it is trivia time...

what is wrong with this SQL  statement?

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"

the answer is in the SQL statement... who ever gets it right gets the 500 points
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Top Expert 2006
Well in this question you asked how to get 0 as prefix and Conver to varchar is the way to do so. The result of your query will depend on the ASP variable values and the contents in the table. Since you have both AND and OR in the statements it might not do exactly what you think unless you enclose those in parenthesis to make sure the order in which they apply. Otherwise the SQL looks good. You might have intended to write

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"

but I am not sure because you haven't told your requirement about this query.
Was it a parentheses issue?

That is, should it have been:

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
Top Expert 2006

That is exactly what I guessed :) The way you had it before the OR were applying to the whole query while you wanted it to apply all ORs first and then ANDed the result with more conditions.

You can get the refund by posting a zero point question in http://www.experts-exchange.com/Community_Support/ with link to this question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial