select DATEPART(m , getdate()) as 月份,datediff(dd , getdate(), dateadd(mm, 1,getdate())) as 天数
declare @m int set @m=2 --月份 select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15') --另外,取得本月天数 select datediff(day,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate()) as varchar)+'-15' ,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate())+1 as varchar)+'-15') --或者使用计算本月的最后一天的脚本,然后用DAY函数区最后一天 SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
declare @a datetime,@b datetime set @a=getdate() set @b=convert(varchar(8),@a,120)+'01' select datepart(mm,@a)as 月份,datediff(day,@b,dateadd(mm,1,@b))as 天数
DECLARE @date smalldatetime SET @date = '2008-7-30' SELECT 32-DAY(@date+(32-DAY(@date)))
头一次见 写个函数也行,调用方便 create function f_dofm(@A VARCHAR(4),@B VARCHAR(2)) returns int as begin return (SELECT DAY(DATEADD(D,-1,DATEADD(M,1,@A+'-'+@B+'-01')))) end ------------------- select dbo.f_dofm('2008','2') --------------------- 29
select DATEPART(m , getdate()) as 月份,datediff(dd , getdate(), dateadd(mm, 1,getdate())) as 天数
set @m=2 --月份
select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15')
--另外,取得本月天数
select datediff(day,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate()) as varchar)+'-15' ,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate())+1 as varchar)+'-15')
--或者使用计算本月的最后一天的脚本,然后用DAY函数区最后一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
set @a=getdate()
set @b=convert(varchar(8),@a,120)+'01'
select datepart(mm,@a)as 月份,datediff(day,@b,dateadd(mm,1,@b))as 天数
set @time ='2008/07/05'
select datediff(d,convert(char(7),@time,120)+'/01',dateadd(m,1,convert(char(7),@time,120)+'/01'))
要考虑年?不用吧
就下个月初减上个月初的就OK了1楼的会在上个月天数比下个月的天数大的时候会出错
例如:
declare @a datetime
set @a='2008-01-31'
select DATEPART(m , @a )as 月份,datediff(dd , @a, dateadd(mm, 1,@a)) as 天数月份 天数
----------- -----------
1 29(所影响的行数为 1 行)
SET @date = '2008-7-30' SELECT 32-DAY(@date+(32-DAY(@date)))
写个函数也行,调用方便
create function f_dofm(@A VARCHAR(4),@B VARCHAR(2)) returns int as
begin
return (SELECT DAY(DATEADD(D,-1,DATEADD(M,1,@A+'-'+@B+'-01'))))
end
-------------------
select dbo.f_dofm('2008','2')
---------------------
29