Yes CTEs have their role but I'm wondering if it's a replacement in an highly optimized star schema warehousing solution.

On the plus side, the date format in the CTE function can take any number of formats -- a wonderful feature for ETL applications. I use this method in almost all ETL code.