Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Month Name


Month Name

Author
Message
Kartik M
Kartik M
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 219
Comments posted to this topic are about the item Month Name

Thanks & Regards,
Kartik M Kumar..
peter-757102
peter-757102
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 2553
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.
Kartik M
Kartik M
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 219
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..
Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 1984
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.

_____________________________________________________________________
MCSA SQL Server 2012
peter-757102
peter-757102
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 2553
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.
peter-757102
peter-757102
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 2553
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.
Kartik M
Kartik M
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 219
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..
Kartik M
Kartik M
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 219
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..
Damiano-463671
Damiano-463671
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 121
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
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10120 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search