Running a sql statement between two dates

[fintan]

Hi I was wondering how to run a sql statement between two date ranges in a database. Here is what I have so far

SELECT callID, datelogged, user, Section, description, Name, dateclosed
FROM closedcalls
WHERE Section = 'MMColParam' AND ((closedcalls.datelogged) BETWEEN #vardatelogged# AND #vardateclosed#)
ORDER BY callID DESC

Here are the variables
Request.QueryString("Section")
and
Request.QueryString("vardatelogged")
and
Request.QueryString("vardateclosed")

Section = G.S.A.
vardatelogged = 01/01/00
vardateclosed = 01/001/02

Can anyone tell me what I'm doing wrong. I'm trying to run a report between two dates and have it displayed in a browser. Thanks
fintan

[dingman]

May I ask which database server you are using? They all have different dialects of SQL, so that may make a difference. I'm afraid I may not be much use, since that looks like ASP to me, but someone should catch it. "With enough eyes, all bugs are shallow." -- I don't remember who I'm quoting there.

[dingman]

Testing in a Postgres database, I found that the format of my time strings made a difference:

select subj, sent from Messages_messages where (sent between '7/14/02' and '7/20/02');
returned no results, and no errors.

select subj, sent from Messages_messages where (sent between 'July 14, 2002' and 'July 20, 2002');
returned 25 results.

It's not an explanation, but it might point to a fix.

[fintan]

Sorry I should have said that I am using IIS 5 with an access 2000 database.

[ukgimp]

Create the query actaully in access using the design query. You will have to make up two dates that you know you have info between. Then toggle to SQL and copy and past in into Ultradev or MX and relace the two date in the sql with the varaibles you wish to assign to it.

Cheers

[fintan]

Cracked it

SELECT callID, datelogged, user, Section, description, Name, dateclosed
FROM closedcalls
WHERE Section = 'MMColParam' AND datelogged BETWEEN #vardatelogged# AND #vardateclosed#
ORDER BY callID DESC

Here are the variables
Request.QueryString("Section")
and
Request.QueryString("datelogged")
and
Request.QueryString("dateclosed")

Section = G.S.A.
vardatelogged = 01/01/00
vardateclosed = 01/001/02

But I'm still having problems passing the dates from the search page to the results page. Do I create a session variable on the search page and a request variable on the results page or do I use a form parameter.

[ukgimp]

Wouldnt have thought you would need a session. Can you stuff it in a hidden form field and then request it in the page that does the work.

[fintan]

I'm on a roll today, right the dates r in this format dd/mm/yyyy but some of the dates in the database are messed up not my fault I just working with what I'm given.

Now it is sorting them by dates but not the way I want, It's doing it by month and year but not day.

[bonzibudy]

Ive had that happen to 500 dates in my database using access 2000. No warning and i still dont know what caused it.

I use session LCID on my asp pages and all of the date setting on the PC are set correctly.

Weird!

[fintan]

Well all I deal with is frontend as long as it works I don't mind.

[fintan]

I'm posting this just to show others how it was done.

SELECT newdateclosed, callID, datelogged, user, Section, SectionID, description, Name, staff_number
FROM closedcalls
WHERE Section LIKE 'MMColParam' AND staff_number LIKE 'varstaff_number' AND (datelogged BETWEEN format(#vardatelogged#, 'mm/dd/yyyy') AND format(#varnewdateclosed#, 'mm/dd/yyyy'))
ORDER BY callID DESC

MMColParam = Request.QueryString("Section") / default value = %

varstaff_number = Request.QueryString("staff_number") / default value = %

vardatelogged = Request.QueryString("datelogged") / default value = 01/01/2000

vardateclosed = Request.QueryString("newdateclosed") / default value= 01/01/2010