• 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)