April 27, 2011 at 11:59 pm
I want to insert all month with year between the parameters @fromdate and @todate
fromDate year is 2000 and todate year is 2001 means i want to insert all
months in that year like
(01/2000,02/2000...12/2000 and 01/2001,02/2002,....12/2002 )
... is it possible..
April 28, 2011 at 2:09 am
How about this?
DECLARE @StartYear INT
,@EndYear INT;
SET @StartYear = 2010
SET @EndYear = 2011
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@NumOfMonths INT
SELECT @StartDate = DATEADD(YYYY,(@StartYear -1900) ,0)
,@EndDate = DATEADD(YYYY,(@EndYear -1900) ,0)
,@NumOfMonths = ( @EndYear - @StartYear + 1 ) * 12
; WITH Tens (N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
)
, Hundreds (N) AS
(
SELECT T1.N FROM Tens T1 CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N FROM Hundreds T1 CROSS JOIN Hundreds T2
)
, NumbersTable(N) AS
(
SELECT 0
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM Thousands
)
SELECT DATEADD(MM, N,@StartDate) [Month]
FROM NumbersTable
WHERE N <= @NumOfMonths
April 28, 2011 at 2:31 am
Hi thanks ya its working good thank u so much
April 28, 2011 at 2:46 am
i have come up with an alternative solution... may b this will help... its using while loop. you just have to input the starting and ending year like 2000 and 2002. i have used the while loop...
declare @temp int
declare @fromdate int
declare @todate int
set @fromdate=2000
set @todate=2001
set @temp=1
while (@fromdate<=@todate)
begin
while (@temp<=12)
begin
print cast(@temp as varchar(4)) +'/'+ cast(@fromdate as varchar(4))
set@temp=@temp+1
end
set@temp=1
set@fromdate=@fromdate+1
end
April 28, 2011 at 2:49 am
It's Also Works
April 28, 2011 at 2:50 am
vicki528 (4/28/2011)
i have come up with an alternative solution... may b this will help... its using while loop. you just have to input the starting and ending year like 2000 and 2002. i have used the while loop...
No Vicki, WHILE Loops are bad at many places. When you have a solution that is set-based and uses a Tally Table ( or NUmbers Table), go for it 🙂
April 28, 2011 at 2:52 am
ok sure i vil try them next time...:-)
April 28, 2011 at 7:13 am
Hi
Nice Post....
Skase
April 28, 2011 at 8:11 am
You can also use system table to get this
DECLARE @StartYear INT
,@EndYear INT;
SET @StartYear = 2010
SET @EndYear = 2011
DECLARE @StartDate DATETIME
,@EndDate DATETIME
,@NumOfMonths INT
SELECT @StartDate = DATEADD(YYYY,(@StartYear -1900) ,0)
,@EndDate = DATEADD(YYYY,(@EndYear -1900) ,0)
,@NumOfMonths = ( @EndYear - @StartYear + 1 ) * 12
; WITH NumbersTable(N) AS
( SELECT ROW_NUMBER() OVER(ORDER BY id) RN FROM sys.sysobjects
)SELECT DATEADD(MM, N,@StartDate) [Month] FROM NumbersTable WHERE N <= @NumOfMonths
Note:This will not work on the Tempdb since there will not be any record in sys.sysobject table.
You can use any table you need make sure that table is having some records so that it can generate Row_number.
Thanks
Parthi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply