please consider the following procedure:
'SelDate' is a date (string) in yyyymmdd format. I would like to select a distinct month. Any suggestions?
Thanks in advance,
asked Jan 17, 2010 at 10:36 AM in Default
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:
or is the function in the WHERE going to limit performance badly?
answered Jan 18, 2010 at 11:40 AM
Maybe I'm being over-simplistic, but
Would return you the
You could do it in a more strongly typed manner:
That would return you the first day of the month as a
answered Jan 17, 2010 at 10:52 AM
Matt Whitfield ♦♦
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.
This will let you put an index on the SelDate field (actually, for the query provided, this index would be better).
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.
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.
answered Jan 17, 2010 at 10:06 PM
select datepart(month,CONVERT(datetime,SelDate)) as SelMonth
answered Jan 18, 2010 at 01:54 PM
Thank you very much! Very helpfull. (i need to figure out how to login as the person i used to ask the question..)
answered Jan 18, 2010 at 05:12 PM