Group By 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Month

Author

Topic

acko
Yak Posting Veteran

52 Posts

Posted - 2003-06-03 : 07:56:20

Hi everybody
I have got the folowing problem
Here is the stored proc which accept two parameters

alter proc SampleProc
@Date1 as datetime, @Date2 as datetime
as

SELECT YEAR (o.orderdate) as C1,
month(o.orderdate) as C2,
SUM (OrderID) as C3
FROM Orders o
WHERE o.orderdate between @Date1 and @Date2
GROUP BY YEAR (o.orderdate), month(o.orderdate)
ORDER BY YEAR (o.orderdate), month(o.orderdate)

exec SampleProc '1996.08.21', '1997.08.21'

Here what I realy like to get in the first column
'1996.08.21' - '1996.08.31' -- because '1996.08.21' is the begining of the criteria - @Date1 and
-- '1996.08.31' is the last date of the August
the next row has to be:
'1996.09.01' - '1996.09.30' --from the first to last date in the next month, Semptember
.
.
.
.
.
and the last row has to be:
'1997.08.01' - '1997.08.21' -- from the first date in the last month to the end of the Criteria - @Date2

I saw a few function for dateweek calculations but i cannot implement similar rules here.
Thanks in advance
Alex

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-03 : 08:02:57

declare @d datetime
set @d = getdate()

/*First Day of Month*/
SELECT dateadd(mm,datediff(mm,0,@d),0)

/*Last Day Of Month*/
SELECT dateadd(mm,datediff(mm,0,@d) + 1,0) - 1

acko
Yak Posting Veteran

52 Posts

Posted - 2003-06-03 : 09:29:34

Thanks but how can i implement this in my problem?

quote:

declare @d datetime
set @d = getdate()

/*First Day of Month*/
SELECT dateadd(mm,datediff(mm,0,@d),0)

/*Last Day Of Month*/
SELECT dateadd(mm,datediff(mm,0,@d) + 1,0) - 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources