/****** Script for SelectTopNRows command from SSMS  ******/
set language N'русский'
with mycte as (
select cast('2007-04-16' as datetime) DateValue
union all
select DateValue + 1 from mycte
where DateValue + 1 < '2007-04-23'
)
select
cast( mycte.DateValue as date)
, datename(dw, mycte.DateValue) [day]
, datepart(dw, mycte.DateValue-1) [day of week]
, datepart(dd, mycte.DateValue) [day of month]
, datepart(ww, mycte.DateValue) [week]
, datepart(mm, mycte.DateValue) [month]
, datename(mm, mycte.DateValue) [monthname]
, datepart(qq, mycte.DateValue) [quarter]
, datepart(yy, mycte.DateValue) [year]
from mycte
OPTION (MAXRECURSION 0)