SQL Statement with Between Dates

[Corneliu]

Tell me what I am doing wrong here. Maybe I dont have the statement correct.
I am trying to get all the records that are between this date and that date. Ex:
User Input:
User 1
11/10/2002
11/19/2002

My table structure has only one date, basically the date that the work has been completed, no other date. I know how to do it with one date, but I never did it with 2 dates. Anyone can help me here.

SQL:
<%
UserID = TRIM( Request.QueryString( &quot;UserID&quot )
SDate = TRIM( Request.QueryString( &quot;SDate&quot )
EDate = TRIM( Request.QueryString( &quot;EDate&quot )

Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
SqlString = &quot;SELECT Main.Technician, SUM(Main.TSpent) as TimeSpent, Main.Category, Main.Date1, Technicians.Technician as Tech_Name, Category.Category as Category_Name &quot;
SqlString = SqlString & &quot; FROM (Main INNER JOIN Technicians ON Main.Technician = Technicians.TechID) INNER JOIN Category ON Main.Category = Category.CatID &quot;
SqlString = SqlString & &quot; WHERE Technicians.TechID = &quot; & UserID & &quot; and Main.Date1 <= &quot; & SDate & &quot; and Main.Date1 >= &quot; & EDate
SqlString = SqlString & &quot; Group by Main.Category, Category.Category, Main.Technician, Technicians.Technician, Main.Date1 &quot;
SET RS = objConn.Execute( sqlString )

%>

 

[palbano]

>> I know how to do it with one date, but I never did it
>> with 2 dates.

Then you need to learn it using your database tool not in an ASP script. Then after you know how the SQL works it is simple to build it in ASP.

-pete

 

[mwa]

use: between 'MM/DD/YY' and 'MM/DD/YY'

instead of >= and <=

make sure you strip off the time (if you have it) from your date. Also, I noticed in your SqlString, you did not put single quotes around some of your char fields. That will probably cause you a problem as well... So your code should look like this:

SqlString = &quot;SELECT Main.Technician, SUM(Main.TSpent) as TimeSpent, Main.Category, Main.Date1, Technicians.Technician as Tech_Name, Category.Category as Category_Name &quot;
SqlString = SqlString & &quot; FROM (Main INNER JOIN Technicians ON Main.Technician = Technicians.TechID) INNER JOIN Category ON Main.Category = Category.CatID &quot;
SqlString = SqlString & &quot; WHERE Technicians.TechID = '&quot; & UserID & &quot;' and Main.Date1 between '&quot; & SDate & &quot;' and '&quot; & EDate & &quot;'&quot;
SqlString = SqlString & &quot; Group by Main.Category, Category.Category, Main.Technician, Technicians.Technician, Main.Date1 &quot;
Response.Write SqlString

Hope that helps...
mwa

 

[mwa]

palbano is right also...

 

[Corneliu]

Thanks guys, it made me look closer to my query and found the problem.
I used the wrong <> in the WHERE statement.
<= &quot; & SDate & &quot; and Main.Date1 >= &quot; & EDate Old One
>= '&quot; & SDate & '&quot; and Main.Date1 <= '&quot; & EDate & &quot;'&quot; The working one.

It works now. I thank you guys.
Just been a VERY LONG DAY...

Thanks Again.