【转】SQL Server 日期算法

 一周的第一天

select @@DATEFIRST

一个月的第一天

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 case day ( dateadd (mm, 2 , dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()), 0 ))))

when 28 then ' 平年 ' else ' 闰年 ' end

一个季度多少天

declare @m tinyint , @time smalldatetime

select @m = month ( getdate ())

select @m = case

when @m between 1 and 3 then 1

when @m between 4 and 6 then 4

when @m between 7 and 9 then 7 else 10 end

select @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 '

select datediff ( day , @time , dateadd (mm, 3 , @time ))