Post reply

Month Name

  • February 19, 2011 at 1:26 pm

    #247053

    Comments posted to this topic are about the item Month Name

    Thanks & Regards, Kartik M Kumar..

  • February 21, 2011 at 2:40 am

    #1289328

    I got two short comments as I am not going to make this a long explaining story.

    1. Take a look at the build in function called dateName in combination with dateAdd

    Example: select dateName( month, dateAdd( month, 12 - 1, 0 ) );

    Will return: December

    2. Try to avoid scalar functions as they are very expensive in SQL Server.

    A scalar function is a function such as yours, that returns a datatype other then a table. In turn functions returning tables come in two forms, inline and non-inline. The difference is that in inline functions the body of the function is exposed to the query using it and thus optimised as part of that query.

    As a result, the only types of function that give good performance over moderate to large datasets are build in functions and inline table-valued functions. All else will hog SQL server down and grind it to a halt when used intensivly. There are special cases where a non-inline table-valued function will perform better if used right. But that has to do with storing a large intermediate result in indexable form for use in the consuming query.

  • February 21, 2011 at 4:04 am

    #1289341

    hi peter,

    The function DATENAME( datepart, date ) alone is enough to return the required data [MonthName], Is it necessary to use the combination of DateADD function too??

    Thanks & Regards, Kartik M Kumar..

  • February 21, 2011 at 4:15 am

    #1289347

    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.

  • February 21, 2011 at 4:30 am

    #1289351

    Kartik M (2/21/2011)

    hi peter,

    The function DATENAME( datepart, date ) alone is enough to return the required data [MonthName], Is it necessary to use the combination of DateADD function too??

    Indeed, I just added the dateadd function as the argument to the function in the article was not a date, but a code representing a number.

  • February 21, 2011 at 4:38 am

    #1289356

    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.

  • February 21, 2011 at 6:03 am

    #1289388

    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.

    Hi Richards,

    As Peter says, The built in function DateName() is available in SQL 2000 too... 🙂

    Thanks & Regards, Kartik M Kumar..

  • February 21, 2011 at 6:06 am

    #1289391

    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..

  • February 21, 2011 at 7:53 am

    #1289431

    Function MonthNamex(@mm numeric (2))

    as

    select case when @mm not between 1 and 12 then 'Invalid Month' else datename(mm,str(@mm)+'/04/2011') end

  • May 17, 2016 at 6:54 am

    #1879507

    Thanks for the script.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply