CODEWITHSUNDEEP
sql-server-2005
user1820065
user1820065

Reputation: 75

SQL 2005 fetching data for current year

I have a view that holds sales data from 2012 till date, I need to write the query that show me only current year sales (2013).

This is what I tried in the first stage

SELECT * 
FROM [Sales_Data] 
WHERE invoiceDate  between '2013-01-01' and '2013-12-31'

this query takes 2 sec to load the data,I though to change the query to fetch data that not required me to update it manually and this is what I found on the Net:

select * from [Sales_Data]  
where datepart(yyyy,invoiceDate) =datepart(yyyy,getdate()) 
and datepart(yyyy,invoiceDate) =datepart(yyyy,getdate())

As a result this query takes much longer to show the data (9 sec). Please let me know if there is a better query to define and get data in less time ?

Upvotes: 1

Views: 698

Answers (1)

Glenn Stevens
Glenn Stevens

Reputation: 2008

Your second query requires sql server to perform a calculation for each row you are querying against.

The following query more closely matches your original select statement.

select * from [Sales_data]
where invoiceDate between DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) --- First Day of current year
and DATEADD(MILLISECOND, -3,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) --- Last Day of current year

Depending on what is going on with the view you are querying against, you may also benefit from having an index which includes the invoiceDate field.

You may want to check the execution plan generated when you run your query to see what is going on behind the scenes when the query runs.

Upvotes: 2

Related Questions