I do agree with OriginalGriff's statement
Quote:
Check your inputs: the chances are they aren't what you think.
because I set up some dummy data and your code works fine.
I tend to disagree with him on the join to the original table though - what are you going to join to? You can however, join to a sub-query or CTE that does this
select distinct DATENAME(MONTH, feepaiddate) AS [MonthName], MONTH(feepaiddate) as [MonthNo]
FROM tbstufees
(inner join on [MonthNo]
One other point, why are you using ODBC scalar functions just to get the month name? Why not use the built-in SQL functions? e.g.
{fn MONTHNAME(feepaiddate)} AS MonthName
can be replaced with
DATENAME(MONTH, feepaiddate) AS [MonthName]
Your query will run faster.
Also note that I am surrounding the reserved words MonthName and Month with square brackets when they are used as column aliases - this is to signal that they are definitely column names and not the functions with the same name. Or note that I used MonthNo instead of Month avoiding the problem altogether