How to distinct select a yyyymmdd format by month?

The string format is ISO compliant so why not CAST it to a date type and then use the MONTH function? To get all January sales use:

SELECT     SelDate, TotalAA, TotalAG
FROM         (SELECT   TOP 20 SelDate, TotalAA, TotalAG
                       FROM  History
                       WHERE (UserId = @UserId)
                       ORDER BY Datum DESC) AS derivedtbl
where MONTH(CAST(seldate as DATEITME)) = 1
ORDER BY SelDate

or is the function in the WHERE going to limit performance badly?

Maybe I'm being over-simplistic, but

LEFT(SelDate, 6) + '01'

Would return you the varchar representation of the first day of the month.

You could do it in a more strongly typed manner:

DATEADD(m, DATEDIFF(m, 0, convert(datetime, SelDate)), 0)

That would return you the first day of the month as a datetime type

Your comment "I would like to select a distinct month" is potentially confusing. I think you mean that you want to filter your results to a specific month, rather than choosing the Distinct months that are returned.

Because your SelDate field is a string, you may like to pass in your month in a YYYYMM format, and use a filter like:

WHERE SelDate LIKE @Month + '%'

This will let you put an index on the SelDate field (actually, for the query provided, this index would be better).

CREATE INDEX ixHistoryByMonth ON (UserID, SelDate) INCLUDE (TotalAA, TotalAG, Datum);

It's worth indexing first on UserID, because you filter on that directly. Next would be SelDate, which is like the phone directory filtering next on FirstName. Unfortunately, the query wants the data in Datum order, so this other query might be preferable in some ways, as the data from the index will come back in order, just not filtered sufficiently. This second index will let the TOP be done more efficiently.

CREATE INDEX ixHistoryByMonth2 ON (UserID, Datum) INCLUDE (TotalAA, TotalAG, SelDate);

The choice between indexes here depends entirely on the statistics of your system, and it could be worth having both - or even asking yourself why you want the TOP 20 by Datum, reordered by SelDate.

The WHERE clause on SelDate is the nicest way of filtering the data to a specific month though, as it is most compatible with indexing.

select datepart(month,CONVERT(datetime,SelDate)) as SelMonth

Thank you very much! Very helpfull. (i need to figure out how to login as the person i used to ask the question..)

steve