June 1, 2010 at 1:45 am
Hi,
I want query to display year ranging form 1985 to current year And another separate query from Jan to Dec. How to do this?
June 1, 2010 at 2:05 am
declare @i int
set @i = 1984
while(@i <= year(getdate()))
begin
select @i
set @i = @i + 1
end
I have used the above query
June 1, 2010 at 2:22 am
Sachin, this might help you:
DECLARE @DATE DATETIME
SET @DATE = '01-01-1985'
SELECT DATEPART(YY,DATEADD(M,N-1,@DATE)) [YEAR], DATENAME(MM,DATEADD(M,N-1,@DATE)) [MONTH]
FROM Tally
WHERE DATEPART(YY,DATEADD(M,N-1,@DATE)) <= YEAR(GETDATE())
To know how to create Tally table, refer the following link from Jeff Moden.
June 1, 2010 at 2:26 am
If you cannot create a Tally table, you can make of this code to create a CTE for Tally Table and then use the real code to generate the desird results:
DECLARE @DATE DATETIME
SET @DATE = '01-01-1985'
;WITH Tens (N) AS
(
SELECT 0 N UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
Thousands (N) AS
(
SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3
),
Tally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) N FROM Thousands
)
SELECT DATEPART(YY,DATEADD(M,N-1,@DATE)) [YEAR], DATENAME(MM,DATEADD(M,N-1,@DATE)) [MONTH]
FROM Tally
WHERE DATEPART(YY,DATEADD(M,N-1,@DATE)) <= YEAR(GETDATE())
Hope this helps!
June 1, 2010 at 2:50 am
Thank u coldcoffee. I will try with your first logic as it seems to be simple and smaller.
With Regards
sachin
June 1, 2010 at 3:05 am
sachinrshetty (6/1/2010)
Thank u coldcoffee. I will try with your first logic as it seems to be simple and smaller.With Regards
sachin
More than simple and small, that code is highly-efficient for large datasets. If you tweak some more of those DATEADD's or DATEPARTs, you can get a lightning fast code!
And, btw, you're welcome sachin!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply