Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Omprakash
Starting Member
4 Posts |
 Posted - 2014-01-02 : 07:08:37
|
Hi,
I have a sql query with a where condition that looks like;
select xxxxx
from xxxx
where duedate between dateadd(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/*First day of last month*/ AND dateadd(second,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/*last day of last month*/
This query takes very long time to execute.
If I update the second part of the between operator i.e.,last day of last month function with a simple getdate() function, it takes less than 4 seconds and returns around 100000 rows.
Can anyone please let me know why the query takes long time to run when such function is used on the second part of the between operator?
I have also tried some thing like
select xxxxx
from xxxx
where duedate >= dateadd(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/*First day of last month*/ AND duedate <= dateadd(second,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))/*last day of last month*/
It is still taking longer. Can someone please help?
Thanks,
Omprakash |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-01-02 : 08:57:17
|
[code]
SELECT *
FROM YourTable
WHERE DueDate >= DATEADD(month, DATEDIFF(month, '19000201', CURRENT_TIMESTAMP), 0)
AND DueDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
[/code] |
 |
|
|
Omprakash
Starting Member
4 Posts |
Posted - 2014-01-02 : 11:27:22
|
Hi Ifor,
Thanks for the reply, but it didnt solve the problem. The query is still running slow.
Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-02 : 12:11:23
|
quote:
Originally posted by Omprakash
Hi Ifor,
Thanks for the reply, but it didnt solve the problem. The query is still running slow.
Thanks
Then I suggest you analyze execution plan to see costly steps
The best way to implement a date range search is previous suggestion
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-01-06 : 02:11:17
|
Is DueDate column indexed?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Between Dates Operator