Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Month Name Expand / Collapse
Author
Message
Posted Saturday, February 19, 2011 1:26 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:28 AM
Points: 89, Visits: 159
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:14 AM
Points: 334, Visits: 2,272
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:28 AM
Points: 89, Visits: 159
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 2,197, Visits: 1,762
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:14 AM
Points: 334, Visits: 2,272
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:14 AM
Points: 334, Visits: 2,272
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:28 AM
Points: 89, Visits: 159
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..
Post #1067059
Posted Monday, February 21, 2011 6:06 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:28 AM
Points: 89, Visits: 159
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:54 PM
Points: 39, Visits: 114
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