October 28, 2008 at 11:30 am
Hi All,
I know I am close but still not quite there...:crazy:
I want to generate some rows, specifically the last 6 months as just months....i.e....
Date
-----
Sep
Aug
Jul
Jun
May
April
Code Posted below but returns nothing...
DECLARE @myDate AS NVARCHAR(50)
SET @myDate = GETDATE()
SELECT MONTH(@myDate) As Date
WHERE @myDate BETWEEN (LEFT(DATENAME(mm, (DATEADD(mm, DATEDIFF(mm,0,@myDate), -6))),3)) AND (LEFT(DATENAME(mm, DATEADD(mm, DATEDIFF(mm,0, @myDate), -1)),3))
October 28, 2008 at 11:52 am
Hi
Here is a possible answer, I am not saying it is optimal but it will return the last six months for you.
DECLARE @Months TABLE (Date VARCHAR(20))
DECLARE @Count INT
SET @Count = 1
WHILE @Count <=6
BEGIN
INSERT INTO @Months( Date )
SELECT DATENAME(mm, DATEADD(mm, @Count - @Count - @Count, GETDATE()))
SET @Count = @Count + 1
END
SELECT * FROM @Months
I hope this is helps.
Regards
Richard...
http://www.linkedin.com/in/gbd77rc
October 28, 2008 at 2:47 pm
Here is some code without a loop:
with SixValues (
num
) as (
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6
)
select
datename(mm,dateadd(mm,datediff(mm, 0, getdate()) - num, 0))
from
SixValues
order by
num asc;
October 29, 2008 at 7:34 am
Here is a dynamic procedure that should work better, since you can reuse it with a different parameter
CREATE PROCEDURE SelectLastXMonths ( @nbMonths AS INT )
as
BEGIN
SELECT TOP ( @nbMonths )
datename(mm,
dateadd(mm, datediff(mm, 0, getdate()) - ROWNum.ID, 0))
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [object_ID] ) AS ID
FROM sys.objects
) AS ROWNum
END
GO
EXEC SelectLastXMonths 6
GO
DROP PROCEDURE SelectLastXMonths
Cheers,
J-F
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply