Good test Jeff. Thank you for putting that together. Just for the fun of it, I plugged my 1st solution into your harness and it turns out the 1st method is is ~90 ms faster than the second. It's still not as fast as your's or Scott's, but still an unexpected improvement.
I hadn't considered the possibility of an invalid YYYY-MM value either, so rather than allowing it to error out on a single bad value, I replaced the CAST with TRY_CAST in the 2nd solution. No discernible difference difference in the TRY_CAST & CAST in terms of speed but something to consider if invalid values is a concern.
I never fail to be amazed by the the things that can be accomplished using integer math... +1000 to you and Scott for using it in your solutions!
(1000000 rows affected)
========================================================================================================================
-- Thom's method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 797 ms, elapsed time = 799 ms.
========================================================================================================================
-- Scott's method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 239 ms.
========================================================================================================================
-- Jason's 1st (DATEFROMPARTS) method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 359 ms, elapsed time = 366 ms.
========================================================================================================================
-- Jason's 2nd (CAST) method redacted for the common #TestTable
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 445 ms.
========================================================================================================================
-- Jason's 3rd (TRY_CAST) 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 = 453 ms, elapsed time = 449 ms.
========================================================================================================================
-- Jeff's method
========================================================================================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 244 ms.