Query between dates but group by month

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 2000 Forums

Transact-SQL (2000)

Query between dates but group by month

Author

Topic

jonasggg
Starting Member

4 Posts

Posted - 2008-05-22 : 11:20:42

hello, how can I query dates using "between" function but grouped by months? for example:

QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008

RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345

Thanks in advance-!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-22 : 11:29:20

If you google "group by month", the very first result is:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-22 : 11:31:32

Don't use "between" for date range queries.

Use >= StartDate and < EndDate+1 day.


select
	-- First day of month
	dateadd(month,datediff(month,0,Mydate),0) ,
	sum(MyMoney)
from
	MyTable
where
	MyDate >= '20071215' and
	MyDate <  '20080216'
group by
	-- Group by first day of month
	dateadd(month,datediff(month,0,Mydate),0)
order by
	-- Order by first day of month
	dateadd(month,datediff(month,0,Mydate),0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources