declare @year int, @month int
set @year = 2006
set @month = 2
declare @d datetime
set @d = cast(cast(@year as varchar(4)) + '-' + cast(@month as varchar(2)) + '-01' as datetime)
select datediff(day, @d, dateadd(month, 1, @d))
set @year = 2006
set @month = 2
declare @d datetime
set @d = cast(cast(@year as varchar(4)) + '-' + cast(@month as varchar(2)) + '-01' as datetime)
select datediff(day, @d, dateadd(month, 1, @d))
returns int
as
Begin
declare @time datetime
set @time=convert(varchar(10),cast(@year as varchar)+'-'+cast(@month as varchar)+'-1',120)
Return datediff(day,cast(month(@time) as varchar)+'-'+cast(month(@time) as varchar)+'-15',cast(month(@time) as varchar)+'-'+cast(month(@time)+1 as varchar)+'-15')
End--测试
select dbo.GetMonthdays(2006,2)
/*
-----------
28(所影响的行数为 1 行)
*/
go
create function GetMonthdays(@year int, @month int)
returns int
as
begin
declare @d datetime
set @d = cast(cast(@year as varchar(4)) + '-' + cast(@month as varchar(2)) + '-01' as datetime)
return(datediff(day, @d, dateadd(month, 1, @d)))
end
go
select dbo.GetMonthdays(2006,1) as [2006年01月天数]--31
select dbo.GetMonthdays(2006,2) as [2006年02月天数]--28
drop function GetMonthdays
你的那个有问题,如果是12月会报错的,要用dateadd(month, 1, @d)加一个月才行
go
create function GetMonthdays(@year int, @month int)
returns int
as
begin
declare @d datetime
set @d = cast(cast(@year as varchar(4)) + '-' + cast(@month as varchar(2)) + '-01' as datetime)
return(datediff(day, @d, dateadd(month, 1, @d)))
end
go
select '2006-01' as 月份, dbo.GetMonthdays(2006,1) as 天数
union select '2006-02', dbo.GetMonthdays(2006,2)
union select '2006-03', dbo.GetMonthdays(2006,3)
union select '2006-04', dbo.GetMonthdays(2006,4)
union select '2006-05', dbo.GetMonthdays(2006,5)
union select '2006-06', dbo.GetMonthdays(2006,6)
union select '2006-07', dbo.GetMonthdays(2006,7)
union select '2006-08', dbo.GetMonthdays(2006,8)
union select '2006-09', dbo.GetMonthdays(2006,9)
union select '2006-10', dbo.GetMonthdays(2006,10)
union select '2006-11', dbo.GetMonthdays(2006,11)
union select '2006-12', dbo.GetMonthdays(2006,12)
/*
月份 天数
2006-01 31
2006-02 28
2006-03 31
2006-04 30
2006-05 31
2006-06 30
2006-07 31
2006-08 31
2006-09 30
2006-10 31
2006-11 30
2006-12 31
*/
drop function GetMonthdays
declare @year int,@month int
select @year=2008,@month=2
select datepart(d,cast(cast(@year as varchar)+'-'+cast(@month+1 as varchar)+'-01' as datetime)-1)
select @NMonth=4select
case when @NMonth in(4,6,9,11) then 30
when @NMonth = 2 then
case when @NYear % 400 = 0 then 29
when @NYear % 100 = 0 then 28
when @NYear % 4 =0 then 29
else 28
end
else 31
end as Days
go
create function GetMonthdays(@year int, @month int)
returns int
as
begin
return (select
case when @month in(4,6,9,11) then 30
when @month = 2 then
case when @year % 400 = 0 then 29
when @year % 100 = 0 then 28
when @year % 4 =0 then 29
else 28
end
else 31
end as Days)
end
go
select '2006-01' as 月份, dbo.GetMonthdays(2006,1) as 天数
union select '2006-02', dbo.GetMonthdays(2006,2)
union select '2006-03', dbo.GetMonthdays(2006,3)
union select '2006-04', dbo.GetMonthdays(2006,4)
union select '2006-05', dbo.GetMonthdays(2006,5)
union select '2006-06', dbo.GetMonthdays(2006,6)
union select '2006-07', dbo.GetMonthdays(2006,7)
union select '2006-08', dbo.GetMonthdays(2006,8)
union select '2006-09', dbo.GetMonthdays(2006,9)
union select '2006-10', dbo.GetMonthdays(2006,10)
union select '2006-11', dbo.GetMonthdays(2006,11)
union select '2006-12', dbo.GetMonthdays(2006,12)
union select '2004-02', dbo.GetMonthdays(2004,2)
union select '2000-02', dbo.GetMonthdays(2000,2)
union select '1900-02', dbo.GetMonthdays(1900,2)
declare @month int
declare @days int
set @year = 2000
set @month = 2set @month = @month + 1
if @month = 13
set @year = @year + 1
declare @date datetime
set @date = ltrim(str(@year)) + '-' + ltrim(str(@month)) + '-' + '1'
set @date = dateadd(dd,-1,@date)
set @days = day(@date)
select @days
declare @month int
declare @days int
set @year = 2000
set @month = 12set @month = @month + 1
if @month = 13
begin
set @year = @year + 1
set @month = 1
end
declare @date datetime
set @date = ltrim(str(@year)) + '-' + ltrim(str(@month)) + '-' + '1'
set @date = dateadd(dd,-1,@date)
set @days = day(@date)
select @days
改一下,如果是12月把年加1,月设置为1