Days of month function

/************************************************************************
*
* Author:  Rafal Skotak
* Purpose: Generate sequence of dates for the specified month
* Date:    2008-01-14
*
************************************************************************/
if exists(select * from sysobjects where id = object_id('dbo.fn_days_of_month') and xtype = 'IF')
drop function dbo.fn_days_of_month
go

create function dbo.fn_days_of_month(@ye int, @mo int)
returns table
return 
select 
dt_val
from
(
select 
dateadd(dd, value, dateadd(month, @mo - 1, dateadd(year, @ye - 1900, '1900.01.01 00:00:00.000'))) as dt_val 
from
(
select 
(v2 * 4 + v1) * 4 + v0 as value
from
(select 0 as v0 union select 1 union select 2 union select 3) as rs0
cross join
(select 0 as v1 union select 1 union select 2 union select 3) as rs1
cross join
(select 0 as v2 union select 1 union select 2 union select 3) as rs2
) as rs
) as rs2
where
month(dt_val) = @mo
go


if exists(select * from sysobjects where id = object_id('dbo.fn_days_of_month_b') and xtype = 'IF')
drop function dbo.fn_days_of_month_b
go

create function dbo.fn_days_of_month_b(@dt datetime)
returns table
return 
select 
dt_val
from
(
select 
dateadd
(
dd, val ue, 
/* set hour, minute, second and millisecond date part to zero and day date part to 1*/ 
dateadd(day, 1 - datepart(day, @dt), dateadd(millisecond, -datepart(millisecond, @dt), dateadd(second, -datepart(second, @dt),dateadd(minute, -datepart(minute, @dt), dateadd(hour, -datepart(hour, @dt), @dt))))) 
) as dt_val 
from
(
select 
(v2 * 4 + v1) * 4 + v0 as value
from
(select 0 as v0 union select 1 union select 2 union select 3) as rs0
cross join
(select 0 as v1 union select 1 union select 2 union select 3) as rs1
cross join
(select 0 as v2 union select 1 union select 2 union select 3) as rs2
) as rs
) as rs2
where
month(dt_val) = month(@dt)
go


-- examples:

select dt_val from fn_days_of_month_b('2007.12.12 12:12:12.212') order by dt_val

select dt_val from fn_days_of_month(2008, 29) order by dt_val