Month Name

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kartik M Kumar » Month Name

Add to briefcase

Month Name

Expand / Collapse

Author

Message

Posted Saturday, February 19, 2011 1:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:40 PM
Points: 85, Visits: 131

Comments posted to this topic are about the item Month Name

Thanks & Regards,
Kartik M Kumar..

Post #1066808

Posted Monday, February 21, 2011 2:40 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:03 AM
Points: 287, Visits: 1,900

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.

Post #1066988

Posted Monday, February 21, 2011 4:04 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:40 PM
Points: 85, Visits: 131

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

Post #1067005

Posted Monday, February 21, 2011 4:15 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 1,602, Visits: 1,084

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.

Post #1067012

Posted Monday, February 21, 2011 4:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:03 AM
Points: 287, Visits: 1,900

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.

Post #1067017

Posted Monday, February 21, 2011 4:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:03 AM
Points: 287, Visits: 1,900

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.

Post #1067023

Posted Monday, February 21, 2011 6:03 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:40 PM
Points: 85, Visits: 131

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

Smile

Thanks & Regards,
Kartik M Kumar..

Post #1067059

Posted Monday, February 21, 2011 6:06 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:40 PM
Points: 85, Visits: 131

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

Post #1067062

Posted Monday, February 21, 2011 7:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 1:17 AM
Points: 39, Visits: 99

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

Post #1067108

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse