SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Between Dates Operator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Omprakash
Starting Member

4 Posts

Posted - 01/02/2014 :  07:08:37  Show Profile  Reply with Quote
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

590 Posts

Posted - 01/02/2014 :  08:57:17  Show Profile  Reply with Quote

SELECT *
FROM YourTable
WHERE DueDate >= DATEADD(month, DATEDIFF(month, '19000201', CURRENT_TIMESTAMP), 0)
	AND DueDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
Go to Top of Page

Omprakash
Starting Member

4 Posts

Posted - 01/02/2014 :  11:27:22  Show Profile  Reply with Quote
Hi Ifor,

Thanks for the reply, but it didnt solve the problem. The query is still running slow.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/02/2014 :  12:11:23  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/06/2014 :  02:11:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Is DueDate column indexed?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000