I run the next query in my classic asp code and it run perfect. it returns results between the days “from” and “to”
query = 'SELECT DateAndTime, X, Y, Z FROM '+ Request.QueryString("table") + ' where DateAndTime between '+"'"+Request("from")+"'" +' and ' +"'"+Request("to")+"'order by DateAndTime";
However,I cannot find out what is wrong with the next query: I want it to return the results of the last week that are recorded in the database.
query = 'SELECT DateAndTime, X, Y, Z FROM '+ Request.QueryString("table") + ' where DateAndTime between DATEADD("d",-7,(select MAX(DateAndTime) from '+ Request.QueryString("table") + ')) and (select MAX(DateAndTime) from '+ Request.QueryString("table") + ') order by DateAndTime";
Any Ideas?
The query is ok when i run it on Microsoft SQL Server Management studio.
Maybe the problem is at the syntax?
Advertisement
Answer
You are missing a quotation mark before order by DateAndTime
query = 'SELECT DateAndTime, X, Y, Z FROM '+ Request.QueryString("table") + ' where DateAndTime between DATEADD("d",-7,(select MAX(DateAndTime) from '+ Request.QueryString("table") + ')) and (select MAX(DateAndTime) from '+ Request.QueryString("table") + ') "order by DateAndTime";