SQL Server DATENAME function returns Month number instead of name?

I recently came across a query posted by a puzzled developer -
Why does SELECT DATENAME(MONTH,GETDATE()) return '09' instead of the obvious result of 'September'?

The DATENAME function, after all, returns a character string representing the specified datepart of the specified date.

It turns out that if the language environment is set to Japanese, Korean or Traditional/Simplified Chinese (during installation or using SET LANGUAGE), months are stored as numerals instead of words for those languages & that's how they show up.

This can be verified by running the sp_helplanguage system stored procedure which reports information about a language.


Also see:
HOW TO find a column's value without specifying the column name