SQL return list of month

Here is another query which i have modified from my previous post.

This query will list out list of months

SELECT Month(GETDATE()) as MonthNum
UNION
SELECT Month(GETDATE()) - 1 as MonthNum
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
ORDER BY MonthNum DESC


and here is some query to convert month number to month name

Convert Month Number to name SQLServerCurry


And here is a full query:

SELECT Month(GETDATE()) as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE()),-1)) as MonthName
UNION
SELECT Month(GETDATE())-1 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-1,-1)) as MonthName
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-2,-2)) as MonthName
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-3,-3)) as MonthName
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-4,-4)) as MonthName
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-5,-5)) as MonthName
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-6,-6)) as MonthName
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-7,-7)) as MonthName
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-8,-8)) as MonthName
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-9,-9)) as MonthName
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-10,-10)) as MonthName
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-11,-11)) as MonthName
ORDER BY MonthNum DESC

List of Year for MSSQL

Hi, i am current doing some project and i need to get list of year dynamically.

Meaning, i don't want to recode or update list of year in a drop down list

So, i google and found this article

SQL return list of year - StackOverFlow.com

and i used in store procedure and call it within sqldatasource ;)


create a new view and paste this code

SELECT YEAR(GETDATE()) as YearNum
UNION
SELECT YEAR(GETDATE()) - 1 as YearNum
UNION
SELECT YEAR(GETDATE()) - 2 as YearNum
UNION
SELECT YEAR(GETDATE()) - 3 as YearNum
UNION
SELECT YEAR(GETDATE()) - 4 as YearNum
UNION
SELECT YEAR(GETDATE()) - 5 as YearNum
UNION
SELECT YEAR(GETDATE()) - 6 as YearNum
UNION
SELECT YEAR(GETDATE()) - 7 as YearNum
UNION
SELECT YEAR(GETDATE()) - 8 as YearNum
UNION
SELECT YEAR(GETDATE()) - 9 as YearNum
UNION
SELECT YEAR(GETDATE()) - 10 as YearNum
ORDER BY YearNum DESC