Between Dates Operator - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Between Dates Operator

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources