SQL Server 2005 CTE and DateTime parsing

CTE - Common Table Expressions
On various occasions I come with a need to use recursive iterations in a stored procedure.
Since server programing is not what I usually do, I search, find and use CTE (Common Table Expressions)
A wonderfull reference I found in an article by Nigel Rivett on:
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

A sample for a recursive use with recursion position flag:

with MyCTE (r1, r2, i, x)
as (
 select r1 = 1, r2 = 1, i = 0, x = convert(varchar(1000),'hello')
 union all
 select r1 = r1 + 1, r2 = r2, i = 1, convert(varchar(1000),x + 'a') from MyCTE
 where len(x) < 10 and i in (0,1)
 union all
 select r1 = r1, r2 = r2 + 1, i = 2, convert(varchar(1000),x + 'b') from MyCTE
 where len(x) < 10 and i in (0,2))
select r1, r2, x
from MyCTE
order by len(x), x

The output for this is:
1 1 hello
2 1 helloa
1 2 hellob
3 1 helloaa
1 3 hellobb
1 4 hellobbb
1 5 hellobbbb
1 6 hellobbbbb

DateTime parsing
Another issue that parsing DateTime datatype. In SQL 2000 I had to convert the DateTime into varchar/nvarchar (depending on the collation), split (another function) and parse the relevant thing I wanted (day, month, hour etc). In SQL 2005, Microsoft introduced us to a new and usefull function called DatePart. This function does the entire trick.
The way to use it, is just say what you want from the DateTime variable and voila...

DATEPART(datepart, date)
The dateparts can be as follows:

• year (yy, yyyy)
• quarter (qq, q)
• month (mm, m)
• dayofyear (dy, y)
• day (dd, d)
• week (wk, ww)
• weekday (dw)
• hour (hh)
• minute (mi, n)
• second (ss, s)
• millisecond (ms)

Samples can be seen on:
http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx (SQL Server 2005)
http://msdn.microsoft.com/en-us/library/ms174420.aspx (SQL Server 2008)