When I looked at the posted solutions, I was pretty sure that Scott's would be the fastest because of both not using CROSS APPLY and the Integer math he used. I was right.
We can simplify the code a bit by using Thom's original to quickly isolate the pieces, use implicit conversions done auto-magically for Scott's code plus that of the CONCAT function that Jason used to erg out another 9-13% performance improvement. Here's a million row test jig and the code (uses a throw-away variable in each case to take disk and display out of the picture). Wonderful things happen when a group of people get together on a problem
--===== Create and populate a million row test table
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
SELECT TOP 1000000
[YYYY-MM] = CONVERT(CHAR(7),RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+DATEADD(dd,0,'1900'),120)
INTO #TestTable
FROM sys.all_columns ac1,
sys.all_columns ac2
;
CHECKPOINT
;
--===== Wait several seconds for things to "cool off"
WAITFOR DELAY '00:00:05'
;
GO
PRINT REPLICATE('=',120);
PRINT '-- Thom''s method redacted for the common #TestTable';
PRINT REPLICATE('=',120);
DECLARE @Bitbucket CHAR(7);
SET STATISTICS TIME ON;
WITH FormattedDates AS
(SELECT CONVERT(DATE,LEFT([YYYY-MM],4) + RIGHT([YYYY-MM],2) + '01') AS MonthStart FROM #TestTable)
SELECT @Bitbucket = CONVERT(VARCHAR(4),DATEPART(YEAR, MonthStart)) + ' Q' + CONVERT(CHAR,DATEPART(QUARTER, MonthStart))
FROM FormattedDates;
SET STATISTICS TIME OFF;
GO
PRINT REPLICATE('=',120);
PRINT '-- Scott''s method redacted for the common #TestTable';
PRINT REPLICATE('=',120);
DECLARE @Bitbucket CHAR(7);
SET STATISTICS TIME ON;
SELECT @Bitbucket = LEFT([YYYY-MM], 4) + ' Q' + CAST((CAST(SUBSTRING([YYYY-MM], 6, 2) AS tinyint) + 2) / 3 AS varchar(1))
FROM #TestTable;
SET STATISTICS TIME OFF;
GO
PRINT REPLICATE('=',120);
PRINT '-- Jason''s 2nd method redacted for the common #TestTable';
PRINT REPLICATE('=',120);
DECLARE @Bitbucket CHAR(7);
SET STATISTICS TIME ON;
SELECT @Bitbucket = CONCAT(LEFT(td.[YYYY-MM], 4), ' Q', DATEPART(QUARTER, od.OrigDate))
FROM #TestTable td
CROSS APPLY ( VALUES (CAST(td.[YYYY-MM] + '-01' AS DATE)) ) od (OrigDate);
SET STATISTICS TIME OFF;
GO
PRINT REPLICATE('=',120);
PRINT '-- Jeff''s method';
PRINT REPLICATE('=',120);
DECLARE @Bitbucket CHAR(7);
SET STATISTICS TIME ON;
SELECT @Bitbucket = CONCAT(LEFT(td.[YYYY-MM],4),' Q',(RIGHT([YYYY-MM],2)+2)/3)
FROM #TestTable td
SET STATISTICS TIME OFF;
GO
Here's are the results from the run I did.
(1000000 rows affected)
========================================================================================================================
-- Thom's method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 1500 ms, elapsed time = 1511 ms.
========================================================================================================================
-- Scott's method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 500 ms, elapsed time = 493 ms.
========================================================================================================================
-- Jason's 2nd method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 750 ms, elapsed time = 738 ms.
========================================================================================================================
-- Jeff's method
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 447 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.