I did some more testing and as it turns out, much of the cost in slower solutions is in dissecting a date multiple times and/or using an inefficient method. And as often in SQL, there are many ways to skin a cat, each with different performance characteristics.

/* Five methods to get a year as text from a date */

select convert( char(4), getDate(), 112 )

select datename( yyyy, getDate() )

select cast( datepart( year, getDate() ) as char(4) )

select cast( year( getDate() ) as char(4) )

select cast( 1900 + dateDiff( year, 0, getDate() ) as char(4) )

Note the use of format 112 in the above convert, it is a deterministic format, so can be used in deterministic functions.

As it turns out, the last and reletivly complex is the fastest of all and thus even beats the specialised 'datepart' and 'year' functions at their own game. I suspect dateDiff uses an optimised code path, whereas datepart first disects the whole datetime and then takes the date part. The year function is problably nothing more then a wrapper and is internally converted into a datepart.

Here are my timings (test code first, followed by results):

DECLARE @Bitbucket CHAR(8),

@NBitBucket NCHAR(8)

;

PRINT '========== Original Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = CASE DATEPART(mm,SomeDate)

WHEN 01 THEN 'Jan'

WHEN 02 THEN 'Feb'

WHEN 03 THEN 'Mar'

WHEN 04 THEN 'Apr'

WHEN 05 THEN 'May'

WHEN 06 THEN 'Jun'

WHEN 07 THEN 'Jul'

WHEN 08 THEN 'Aug'

WHEN 09 THEN 'Sep'

WHEN 10 THEN 'Oct'

WHEN 11 THEN 'Nov'

ELSE 'Dec'

END + ' ' + CAST(DATEPART(yy,SomeDate) AS CHAR(4))

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== DATENAME Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @NBitBucket = SUBSTRING(DATENAME(mm,SomeDate),1,3) + N' ' + DATENAME(yy,SomeDate)

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== CONVERT Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT(CONVERT(CHAR(11),SomeDate,106),8)

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== Substring + year() Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( year( SomeDate ) as char(4) )

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== Substring + datepart( year ) Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( datepart( year, SomeDate ) as char(4) )

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== Substring + ISO convert of year Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + convert( char(4), SomeDate, 112 )

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

PRINT '========== Substring + datediff (generally fastest) Method of Conversion ==========';

SET STATISTICS TIME ON;

SELECT @Bitbucket = subString( 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ', month( SomeDate ) * 4 - 3, 4 ) + cast( 1900 + dateDiff( year, 0, SomeDate ) as char(4) )

FROM dbo.JBMTest;

SET STATISTICS TIME OFF;

========== Original Method of Conversion ==========

SQL Server Execution Times:

CPU time = 1453 ms, elapsed time = 1463 ms.

========== DATENAME Method of Conversion ==========

SQL Server Execution Times:

CPU time = 1031 ms, elapsed time = 1030 ms.

========== CONVERT Method of Conversion ==========

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 736 ms.

========== Substring + year() Method of Conversion ==========

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 752 ms.

========== Substring + datepart( year ) Method of Conversion ==========

SQL Server Execution Times:

CPU time = 750 ms, elapsed time = 751 ms.

========== Substring + ISO convert of year Method of Conversion ==========

SQL Server Execution Times:

CPU time = 954 ms, elapsed time = 954 ms.

========== Substring + datediff (generally fastest) Method of Conversion ==========

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 723 ms.

It makes me wonder if month( somedate ) can also be optimised as I did with year( somedate )