How to SQL Select Month and Year using T-SQL in order to Display in Combobox
If you want to display a list of years and months on a web page or on an application using a dropdown box or a combo box, all you have to do is binding a range of years to one combo box and binding the months to the other.
If you don't want to use a lookup table for years and months and want to code a t-sql select query which will fill these two month and year combo boxes on the fly, how can we do?
We can solve this sql problem in two ways.
Here is a basic solution which is formed of a t-sql sample code selecting the year range 2005 to 2010 and the month range from 1-12 :
SELECT [year], [month]
FROM (
SELECT 2005 AS [year] UNION ALL
SELECT 2006 UNION ALL
SELECT 2007 UNION ALL
SELECT 2008 UNION ALL
SELECT 2009 UNION ALL
SELECT 2010
) years
CROSS JOIN (
SELECT 1 AS [month] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
) months
ORDER BY years.[year], months.[month]
The solution is simple and the t-sql script is a single sql select statement.
All done is with two subselects, forming one years other months, taking a cross join is solving our problem.
But what happens if we have a user-defined table valued funtion with parameters where we can define the range we want to get also?
Again a cross join will solve the problem just as the above sample sql codes.
I have prepared such a used defined t-sql function named "NumbersTable" where you will find the sql source codes of the function at the related sql article Creating Numbers Table on MS SQL Server 2005 or MS SQL Server 2008 Databases.
Using a t-sql function which returns a list of rows or returns a table will decrease the lines of t-sql codes as shown below :
SELECT
years.i AS [year],
months.i AS [month]
FROM dbo.NumbersTable(2005, 2010, 1) years
CROSS JOIN dbo.NumbersTable(1, 12, 1) months
ORDER BY years.i, months.i
|