• peter-757102 (2/21/2011)


    Richard Warr (2/21/2011)


    DATENAME() is fine for SQL Server 2005 and later. However the article stated that it was for SQL Server 2000 so is of use to those of us working on legacy systems where the built-in function is not available.

    It is an "expensive" function but, if used sensibly solely for reporting/presentation, the overhead should not be prohibitive.

    I did not see an article other then the function listing and a table showing the code to month mapping.

    But given the use case you bring up, there are other optimizations possible too. Most natural is to have a mapping table for the codes. This allows the optimizer to be smart again and not get killed off by the scalar function call overhead.

    Alternativly you can use a varchar constant that lists all month names with each month padded with spaces to make each month the same length. Then it is just a matter of using substring and some math on this varchar to fetch the right substring and then trim it. If you also want to have 'InvalidMonth' as an answer (which is very non relational like), then use: isnull( nullif( result, '' ), 'InvalidMonth' )

    Granted a function reads better, but as I said before the performance hit can be very heavy.

    Hi Peter,

    In the article, no where it was mention that it applicable to SQL2000, but in my script comments, I mentioned the applicable SQL Version as SQL 2000

    Thanks & Regards, Kartik M Kumar..