SQLserver2000时间计算整理

来源：互联网发布：淘宝电子发票怎么申请编辑：程序博客网时间：2024/05/20 02:25

select convert(char(10), dateadd(mm, datediff(mm,0,getdate()),0),120) as '月初'

select convert(char(10),dateadd(wk, datediff(wk,0,getdate()),0),120) as '本周一'

select convert(char(10),dateadd(qq, datediff(qq,0,getdate()),0),120) as '本季度第一天'

select convert(char(10), dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0)),120) as '上月最后一天'

select convert(char(10), dateadd(mm,1,dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0))),120) as '本月最后一天1'
select convert(char(10),dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) ,120) as '本月最后一天2'

select convert(char(10), dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate()),0)+1),120) as '下月第一天'

select convert(char(10), DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0),120) as '本月第一周'
select datename(weekday,getdate()) as '今天星期几'

SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end as '什么年'

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)) as '当前季度多少天'

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/gmj_1019/archive/2006/09/15/1227149.aspx