Data for current month - 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

General SQL Server Forums

New to SQL Server Programming

Data for current month

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2010-03-08 : 14:44:26

Hello,

What is the best way to query for data in the current month?

I'm trying to make

where DateDiff (Month,billdt,GetDate())=1

work for me, but of course this only returns the last month's data.

Am I at least on the right track?

Thanks!

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2010-03-08 : 15:35:05

So I've partially figured this out using GetDate.

select 
	YEAR (billdate) as [Yr], 
	MONTH (billdate) as [Mt],
	tacct as [AccountCode],
	sum (billdate) as [TotalHours]
from time
where MONTH(billdate) = MONTH(getdate())
and YEAR(billdate) = YEAR(getdate())

But surely there is a more highly optimized method of filtering for specific dates??

CSears
Starting Member

39 Posts

Posted - 2010-03-08 : 15:48:24

There isn't much more optimization you can do. After this query goes through the code optimizer you end up comparing the Year and Month values of the date you want to a constant value.

Using the DateDiff statement would have worked if you had checked for values that were =0. The DateDiff returns the difference in time between the two periods, so if you wanted the same month, you would want:

WHERE DateDiff(Year, BillDT, GetDate())=0 AND DateDiff(Month, BillDT, GetDate())=0

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-08 : 16:21:16

This is the be way to handle a date range query:

Select
	*
from
	MyTable
Where
	-- Greater than or equal to start of current month
	billdt >=  dateadd(mm,datediff(mm,0,getdate()),0) and
	-- Before start of next month
	billdt <   dateadd(mm,datediff(mm,0,getdate())+1,0)

Notice that you are asking for greater than or equal to the start of the current month, and less than the start of the next month.

This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources