Dates in where Clause

[CharlieMike73]

Hi, I have a ASP Page that connects to an Oracle 8i instance and i need to limit the select to a date range.

I have tried using the following as advised by oracle text books with no joy...

WHERE DWR_DATE BETWEEN TO_DATE('01-JUL-02','DD-MON-YY') and TO_DATE('17-JUL-02','DD-MON-YY')

Where you see the dates 01-JUL-02 and 17-JUL-02 I need these to be dynamic and that too is causing a problem.

Do i add <%= startDate %> and <%= endDate %> within the query, or get everything from the table/s and then trim the results, because this would take a large amount of time as we are well on our way to a million records.

 

[dds82]

You can build the SQL query dynamically, like this:

strSQL="select ... from myTable where DWR_DATE >= #"
strSQL=strSQL & startDate
strSQL=strSQL & &quot;# and DWR_DATE <= #&quot;
strSQL=strSQL & endDate
strSQL=strSQL & &quot;#;&quot;

set rs=conn.execute(strSQL)

 

[CharlieMike73]

I am connecting to Oracle and it dont like dates to be passed like that. Oracle is VERY STRICT.

Thank you anyway.

Guess I will just have to keep shooting at it, till i hit it, the i will post here for other users!

Wish me luck!

 

[sunila7]

Hi,

Try This......
strSQL = &quot;SELECT * FROM TBL WHERE DWR_DATE BETWEEN TO_DATE('&quot; & startDate & &quot;','DD-MON-YY') and TO_DATE('&quot; & endDate & &quot;','DD-MON-YY')&quot;
set rs=conn.execute(strSQL)


Sunil