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