取某月天数:select day(dateadd(month,1,'2012-02-01')-1)
--当月天数
select day(dateadd(ms,-3,DATEADD(m, DATEDIFF(m,0,getdate())+1,0)))
---当月第一天
select dateadd(d,-day(getdate())+1,getdate())
---当月最后一天
select dateadd(d,-day(getdate()),dateadd(m,1,getdate()))
select dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,getdate()) ))
--本年最后一天
select dateadd(d,-day(getdate()),dateadd(m,12,getdate()))
--当月第一个星期一
SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')
补充:
SQL Server 日期算法一周的第一天:
一个月的第一天:
select dateadd(mm,datediff(mm,0,getdate()),0)
本周的星期一:
select dateadd(wk,datediff(wk,0,getdate()),0)
一年的第一天:
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
季度的第一天:
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
当天的零时:
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
上个月的最后一天:
本月第一天减2ms.
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
本月的最后一天:
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
本月的第一个星期一去掉时分秒:
DATEADD(day, DATEDIFF(day,0,getdate()), 0)
显示星期几:
select datename(weekday,getdate())
如何取得某个月的天数:
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))
判断是否闰年:
SELECT caseday(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when28then'平年'else'闰年'end
一个季度多少天:
declare@mtinyint,@timesmalldatetimeselect@m=month(getdate()) select @m=case when @mbetween 1 and 3 then 1 when @m between 4 and 6 then 4 when @m between 7 and 9 then 7 else 10 endselect @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')
--B. 季度的最后一天(CASE判断法)
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)
--C. 季度的最后一天(直接推算法)
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-M