I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime?

I'm using c#

 Thanks!

                    select distinct datename(month,CreatedDate) from tablename

                                            or

                    select distinct datename(month,cast(CreatedDate as datetime)) from tablename

Thanks. That returns the month. I tried:

SELECT DATENAME(m, GETDATE()) AS 'Month Name'; and that returned "May"

I would like it to return "May 2008". And then when i select that option in the drop down, it just selects those records during that month and year.

select distinct substring(convert(varchar,datecolumn,106),4,10) as months from table

I guess this would work

SELECT DATENAME(m, GETDATE()) + ' ' + DATENAME(yy, GETDATE()) AS 'Month' ?

 But how would I get it the right values?

what is the meaning of           "But how would I get it the right values?".

that means u are asking how to retrieve the values for the selected item as it don't have any id.

if it is case,

select * from the tablename where substring(convert(varchar,datecolumn,106),4,10) = (dropdownlist selected value)

Exactly what i was looking for. Thanks!

Here's my solution

SELECT DISTINCT SUBSTRING(CONVERT (VARCHAR, [dateworked], 106), 4, 10) AS Months
FROM [tasks]

ORDER BY ??????

However, I would like to order those Descendingly by [dateworked], but ORDER BY isn't working, and then I have to include it in the select, and it shows multiple dates with a Month.

any idea?

 try this.

SELECT DISTINCT substring(convert(varchar,dateworked,112),1,6) as Tempdate,
SUBSTRING(CONVERT (VARCHAR, [dateworked], 106), 4, 10) AS Months
FROM [tasks] order by TempDate asc

Don't show tempdate in frontend. it is just used as a helper column.

Perfect.