27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @y int
SET @y=2010 --year
DECLARE @week int, @dt datetime
SET @week=1 --week
set @dt = dateadd(d,(@week) * 7 ,ltrim(@y)+'-01-01')
set @dt = case when datepart(wk,@dt)>@week or datepart(yy,@dt)>@y then dateadd(wk,-1,@dt) else @dt end
SELECT @dt
,DATEADD(Day, case when datepart(wk,DATEADD(Day, -DATEPART(dw,@dt)+1 ,@dt)) != @week then 0 else -DATEPART(dw,@dt)+1 end,@dt),
DATEADD(Day, case when datepart(wk,DATEADD(Day,7-DATEPART(dw,@dt),@dt)) != @week then 0 else 7-DATEPART(dw,@dt) end,@dt)
/*
----------------------- ----------------------- -----------------------
2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000
(1 行受影响)
----------------------- ----------------------- -----------------------
2009-12-31 00:00:00.000 2009-12-27 00:00:00.000 2009-12-31 00:00:00.000
(1 行受影响)
*/
declare @week int
set @week=1
select dateadd(d,(@week-1)*7,'2010-1-1'),dateadd(d,@week*7-1,'2010-1-1')
/*
2010-01-01 00:00:00.000 2010-01-07 00:00:00.000
*/
DECLARE @y int
SET @y=2010 --year
DECLARE @week int, @dt datetime
SET @week=1 --week
set @dt = dateadd(d,@week * 7 ,ltrim(@y)+'-01-01')
set @dt = case when datepart(wk,@dt)>@week then dateadd(wk,-1,@dt) else @dt end
--case when datepart(wk,DATEADD(Day, -DATEPART(dw,@dt)+1 ,@dt)) != @week then 0 else -DATEPART(dw,@dt)+1 end
SELECT @dt
,DATEADD(Day, case when datepart(wk,DATEADD(Day, -DATEPART(dw,@dt)+1 ,@dt)) != @week then 0 else -DATEPART(dw,@dt)+1 end,@dt),DATEADD(Day,7-DATEPART(dw,@dt),@dt)
/*
----------------------- ----------------------- -----------------------
2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 2010-01-02 00:00:00.000
(1 行受影响)
*/
--指定日期所在周的任意星期几
DECLARE @y int
SET @y=2009 --year
DECLARE @week int,@number int, @dt datetime
SET @week=5 --week
set @number = 1 --day
set @dt = dateadd(d,@week * 7 ,ltrim(@y)+'-01-01')
set @dt = case when datepart(wk,@dt)>@week then dateadd(wk,-1,@dt) else @dt end
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
set @number = 7--day
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
/*
-----------------------
2009-01-25 00:00:00.000
(1 行受影响)
-----------------------
2009-01-31 00:00:00.000
(1 行受影响)
*/
--星期六
SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(week,50,'2009-01-01')), 0)+6
--星期日
SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(week,50,'2009-01-01')), 0)-1
/*
------------------------------------------------------
2009-12-20 00:00:00.000
(所影响的行数为 1 行)
------------------------------------------------------
2009-12-13 00:00:00.000
(所影响的行数为 1 行)
*/
--时间的处理
--(1)短时间的处理
declare @S datetime
set @S=getdate()
--select (convert(varchar(10),@S,120))
--select datediff(m,@S,3)
select replace(convert(varchar(10),@S,120),'-0','-')
--长时间的处理
declare @S datetime
set @S=getdate()
select
stuff(
stuff(convert(varchar(10),@s,112),5,0,'年'),8,0,'月')+'日'
--日期第一天
declare @S datetime
set @S=getdate()
select convert(varchar(4),@s,120)+'0101'
--指定日期所在季度的第一天
declare @S datetime
set @S=getdate()
select convert(datetime,
convert(char(6),dateadd(month,datepart(qq,@S)*3-2,dateadd(month,-month(@S),@s)),112)+'01')
--指定日期所在的季度的最后一天
declare @S datetime
set @S=getdate()
select dateadd(day,-1,(convert(datetime,
convert(char(6),dateadd(month,datepart(qq,@S)*3+1,dateadd(month,-month(@S),@s)),112)+'01')))
--指定日期所在月份的第一天
declare @S datetime
set @S=getdate()
select convert(char(8),@s,120)+'01'
--指定日期所在月份的最后一天
declare @S datetime
set @S=getdate()
select dateadd(day,-1,convert(datetime,convert(char(8),dateadd(month,1,@s),120)+'01'))
--指定日期所在周的任意一天
declare @S datetime
set @S=getdate()
select dateadd(day,1,dateadd(day,-datepart(weekday,@s),@s))
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2008/12/11/3501110.aspx