SQL returns date out of range

VolfrikSemisari
VolfrikSemisari used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

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

Commented:
You have to convert to varchar type ...

Convert(varchar(10), EventDate, 101)  

Author

Commented:
WRONG!!

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.

Author

Commented:
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
Commented:
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

Commented:
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