Crud. I just noticed a limitation and I need to post another correction to the article. I tested for all dates from 1900-01-01 00:00:00.000 through 9999-12-31 23:59:59.997. If you have just 3ms more time span, the code fails with an overflow. This is apparently because CONVERT has a limit the same as the MAX SQL DateTime of 9999-12-31-23:59:59.997 (and why wouldn't it?). Even just 3ms more, and you get an overflow error. That means this method has a limit of 3ms less than 10,000 years. Still, that's better than 148 years but it's still an error in the article that needs to be corrected, which I'll do in the morning.

Here's the code that demonstrates the problem above...

--==== This works.

SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1900-01-01 00:00:00.000' AS DATETIME),114)

;

--==== Adding just 3ms more duration causes CONVERT to fail.

SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1899-12-31 23:59:59.997' AS DATETIME),114)

;