Skip to Content
Jan 28, 2014 at 01:38 PM

Between Clause taking more time


Hello All,

I work on business objects reporting tool which gets data from sybase. We got requirement to change date prompts to dynamic which means user gets an input to choose date prompts to select like "Last year,last quater,current month ,last month etc. For example , when user select last week, I am trying to calcualate them from getdate() passing through between clause.

If I write the query as below for 1 day giving the dates as manually , it is running very fast less and I am getting data in seconds

SELECT sum(a.marks) FROM students a WHERE a.daily_Dte BETWEEN ( '2014-01-27' ) AND ( '2014-01-28' )

Now in the "between" clause I have just changed the dates to dynamic way like below and query is taking veryyy long time to execute

SELECT sum(a.marks) FROM students a WHERE a.daily_Dte between dateadd(dd,-1,getdate()) and getdate()

My Sybase version is: Adaptive Server Enterprise/12.5.4/EBF 15432 ESD#8/P/Sun_svr4/OS 5.8/ase1254/2105/64-bit/FBO/Sat Mar 22 14:38:37 2008

Can anyone help me reason behind the query taking very long time to execute. I have just used simple getdate() function to calculate date for yesterday and pass the date dynamically but it is very long time to execute. Is their any setting missing which is causing this issue or do I need to specify anything before I run the query. This is not as Issue with indexing because the query is running fine when we give the dates manually. Can anyone please help.Thank you