declare @i date
set @i = '2011-04-19'
SELECT DATEADD(dd,CASE (DATEPART(dw,@i) - 1) WHEN 2 THEN 7 WHEN 1 THEN 8 WHEN 3 THEN 6 WHEN 4 THEN 5
WHEN 5 THEN 4 WHEN 6 THEN 3 WHEN 7 THEN 2 END ,@i)
--2011-04-26
select DATEADD(week,DATEDIFF(week,'1900-01-02','2011-4-19')+1,'1900-01-02')
--问题1
declare @date datetime
set @date='2011-4-19'
--第一个星期二
select dateadd(day,10-DATEPART(WEEKDAY,@date),@date)
/*
-----------------------
2011-04-26 00:00:00.000
declare @i date
set @i = '2011-04-19'
--1
SELECT DATEADD(dd,CASE (DATEPART(dw,@i) - 1) WHEN 2 THEN 7 WHEN 1 THEN 8 WHEN 3 THEN 6 WHEN 4 THEN 5
WHEN 5 THEN 4 WHEN 6 THEN 3 WHEN 7 THEN 2 END ,@i)
--2011-04-26
;WITH TT
AS(
SELECT DATEADD(dd,number,@i) AS dd
FROM master..spt_values
WHERE type = 'P')
--2
SELECT TOP 1 dd FROM TT WHERE DAY(dd) = 31 AND dd >@i ORDER BY dd ASC
--2011-05-31
--3
SELECT TOP 1 dd FROM TT WHERE DAY(dd) = 28 AND dd >@i ORDER BY dd ASC
--2011-04-28
set datefirst 1
select convert(varchar(10),dateadd(day,9-datepart(weekday,'2011-04-18'),'2011-04-18'),110)
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))
declare @dt datetime
set @dt = '2011-04-19'
SELECT @dt星期几 WHEN 星期 then 本周2
else 下周2 end
select day(@dt) when 31 then 下月31号
else 本月31号 end
select day(@dt) when 大于1 and 小于28 then 本月28号
else 下月28号 end
declare @date datetime
set @date='2011-4-19'
--第一个星期二
select dateadd(day,10-DATEPART(WEEKDAY,@date),@date)
--第一个31号
select Case when MONTH(@date) IN(1,3,5,7,8,10,13) then CONVERT(varchar(7),@date,120)+'-'+'31'
else CONVERT(varchar(7),dateadd(month,1,@date),120)+'-'+'31' end
/*
----------
2011-05-31
declare @date datetime
set @date='2011-4-19'
--第一个星期二
select dateadd(day,10-DATEPART(WEEKDAY,@date),@date)
--第一个31号
select Case when MONTH(@date) IN(1,3,5,7,8,10,12) then CONVERT(varchar(7),@date,120)+'-'+'31'
else CONVERT(varchar(7),dateadd(month,1,@date),120)+'-'+'31' end
--第一个28号
select case when DATEPART(DAY,@date)<28 then CONVERT(varchar(7),@date,120)+'-'+'28'
else CONVERT(varchar(7),dateadd(month,1,@date),120)+'-'+'28' end
/*
-----------------------
2011-04-26 00:00:00.000
(1 行受影响)
----------
2011-05-31
(1 行受影响)
----------
2011-04-28
(1 行受影响)
declare @date datetime
set @date='2011-04-19'
;with cte
as (select @date+number ddate,
DATEPART(dw,getdate()+number-1) dweek
from dbo.spt_values where type='p')
1,
select top 1 * from cte
where ddate>@date and dweek=2
order by ddate
2,
select top 1 * from cte
where ddate>@date and DATEPART(dd,ddate)=31
order by ddate
3,
select top 1 * from cte
where ddate>@date and DATEPART(dd,ddate)=28
order by ddate
declare @date datetime
set @date = '2011-4-19'
set datefirst 1
--1：获得2011-4-19后的第一个星期二（结果应为2011-4-26）
select
case
when 2-DATEPART(Weekday, @date) <= 0 then dateadd(day, 9-DATEPART(Weekday, @date), @date)
else dateadd(day, 2-DATEPART(Weekday, @date), @date)
end
--2：获得2011-4-19后的第一个31号（结果应为2011-5-31，4月没有31号）
--???
--3：获得2011-4-19后的第一个28号（结果应为2011-4-28，每个月都有28号）
select
case
when 28-DATEPART(day, @date) > 0 then DATEADD(day, 28-DATEPART(day, @date), @date)
else DATEADD(day, 28-DATEPART(day, @date) ,DATEADD(month, 1, @date))
end
declare @dt datetime
set @dt = '2011-04-11'
SELECT case datename(weekday,@dt) WHEN 'Monday' then dateadd(day,1,dateadd(wk,datediff(wk,0,getdate()),0))
else dateadd(day,8,dateadd(wk,datediff(wk,0,getdate()),0)) end
declare @dt datetime
set @dt = '2011-04-01'
--select datename(weekday,@dt)
SELECT case datename(weekday,@dt) WHEN 'Monday' then dateadd(day,1,dateadd(wk,datediff(wk,0,@dt),0))
else dateadd(day,8,dateadd(wk,datediff(wk,0,@dt),0)) end
declare @date datetime --日期
declare @Mon int; --星期?
set @date = '2011-4-18';
set @mon=2;
--1
select
case
when @mon-DATEPART(Weekday, @date) <= 0 then dateadd(day, @mon+7-DATEPART(Weekday, @date), @date)
else dateadd(day, @mon-DATEPART(Weekday, @date), @date)
end
--2
select case when MONTH(@date) IN(1,3,5,7,8,10,12) then CONVERT(varchar(7),@date,120)+'-31'
else CONVERT(varchar(7),dateadd(month,1,@date),120)+'-31' end
--3
select case when DATEPART(DAY,@date)<28 then CONVERT(varchar(7),@date,120)+'-28'
else CONVERT(varchar(7),dateadd(month,1,@date),120)+'-28' end
declare @dt datetime
set @dt = '2011-02-20'
--select month(@dt)
SELECT case WHEN month(@dt) in (1,3,5,8,10,12) then dateadd(month,2,@dt)
else dateadd(month,1,@dt) end
declare @dt datetime
set @dt = '2011-02-20'
--select month(@dt)
SELECT case WHEN month(@dt) in (1,3,5,8,10) then dateadd(month,2,@dt)
else dateadd(month,1,@dt) end
declare @dt datetime
set @dt = '2011-12-31'
SELECT case WHEN (month(@dt) in (1,3,5,8,10) and day(@dt)=31) or (month(@dt)=1 and day(@dt) in(29,30)) then dateadd(month,2,@dt)
else dateadd(month,1,@dt) end
