帮你想法中=========================================================
我的回复,尽可能为你分忧解难
BLOG:blog.csdn.net/softj --欢迎光临,有更多信息等着你!
QQ高级群:5063844专研数据库 --大家进来聊一聊!
MSN:[email protected] --这不常用!
Mail:[email protected] --有什么问题可以和我来EMAIL!
=========================================================
我的回复,尽可能为你分忧解难
BLOG:blog.csdn.net/softj --欢迎光临,有更多信息等着你!
QQ高级群:5063844专研数据库 --大家进来聊一聊!
MSN:[email protected] --这不常用!
Mail:[email protected] --有什么问题可以和我来EMAIL!
=========================================================
declare @dd1 datetimeset @dd ='2006-1-12'-- getdate()select @dd 日期,datepart(week,@dd) 周次,datepart(weekday,@dd) 天次set @dd1 = cast( str(datepart(year,dateadd(year,-1,@dd))) +'-'+ str(datepart(month, @dd)) + '-1' as datetime) select @dd1 上年当月第一天,datepart(weekday,@dd1) 天次 --, dateadd(week,datepart(week,@dd),@dd1) ,dateadd(day,datepart(weekday,@dd),dateadd(week,datepart(week,@dd),@dd1))set @dd1 = dateadd(week,datepart(week,@dd)-1,@dd1)
select @dd1 上年同一周,datepart(weekday,@dd1) 天次set @dd1 =dateadd(day,-datepart(weekday,@dd1)+1,@dd1)
select @dd1 上年同周第一天set @dd1 = dateadd(day,datepart(weekday,@dd)-1,@dd1)
select @dd1 最后结果应还有更好的方法!这先应急吧 :)=========================================================
我的回复,尽可能为你分忧解难
BLOG:blog.csdn.net/softj --欢迎光临,有更多信息等着你!
QQ高级群:5063844专研数据库 --大家进来聊一聊!
MSN:[email protected] --这不常用!
Mail:[email protected] --有什么问题可以和我来EMAIL!
=========================================================
declare @dt datetime,@firstdate datetime
set @dt='2006-01-19'
set @firstdate=convert(datetime,convert(varchar(4),year(@dt)-1)+'-01-01',120)
select dateadd(day,datepart(weekday,@dt)-7,dateadd(day,datepart(week,@dt)*7-datepart(weekday,@firstdate),@firstdate))
里面的dateadd求出上一年的第N周的最后一天;
外面的dateadd求出上一年的第N周的星期M的日期。
set @dt='2006-01-19'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120) as int)
set @dt2= @dt2+7*month(@dt)+datepart(weekday,@dt)
select cast(@dt2 as datetime)2005-01-13
set @t='2006-02-22'
select dateadd(d,((datepart(weekday,@t)+@@datefirst-2)%7+1)-
(datepart(weekday,dateadd(week,
datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)/7+
case when datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)%7>0 then 1 else 0 end
,convert(char(8),dateadd(year,-1,@t),120)+'01'))+@@datefirst-2)%7-1,
dateadd(week,
datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)/7+
case when datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)%7>0 then 1 else 0 end
,convert(char(8),dateadd(year,-1,@t),120)+'01'))
------------------------------------------------------
2005-02-23 00:00:00.000(所影响的行数为 1 行)
set @t='2006-01-24'select dateadd(d,1,dateadd(year,-1,@t))减一年,那么天数就加一,应该就可以了!
set @dt='2006-01-19'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120) as int)
set @dt2= @dt2+7*(datepart(wk,@dt)-2)+datepart(weekday,@dt)+
(7-datepart(weekday,convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120)))
select cast(@dt2 as datetime)-----------------------------
2005-01-13
set @t='2006-01-24'
--2005年
select dateadd(d,1,dateadd(year,-1,@t))
--2004年
select dateadd(d,3,dateadd(year,-2,@t))这样推算的,比较有意思但是不适合计算机运算!推荐mislrb(上班看看早报,上上CSDN,下班看看电影)兄的方法,呵呵,简单可靠!
--如查2006年2月第4周星期天是几号declare @selyear varchar(4) --要查的年份
declare @selmonth varchar(2) --要查的月份
declare @selweek int --要查第几周
declare @selweekday int --要查星期几
declare @day int
declare @sel datetime
declare @week int--输入条件
set @selyear='2006' --年份
set @selmonth='02' --月份
set @selweek=4 --第几周
set @selweekday=7 --星期几--查询
set @day=0
set @sel=@selyear + '-'+@selmonth +'-'+'01'
print @sel if datepart(weekday,@sel)=1
begin
set @day=@day+1
end
else
begin
set @day=9-datepart(weekday,@sel)
end
set @week=1
set @day=@day+(@selweek-@week-1)*7+@selweekday
select @selyear +'-'+ @selmonth +'-'+cast(@day as varchar(2))/*结果
2006-02-26
declare @dt datetime,@dt2 int
set @dt='2006-03-16'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(20))+'-01',120) as int)
print @dt2
print cast(datepart(wk,@dt) as int)%4
set @dt2=@dt2+(7-datepart(weekday,convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120)))
+case when cast(datepart(wk,@dt) as int)%4=1 then 0 when cast(datepart(wk,@dt) as int)%4=2 then datepart(weekday,@dt)
when cast(datepart(wk,@dt) as int)%4=3 then
datepart(weekday,@dt)+7 when cast(datepart(wk,@dt) as int)%4=4 then datepart(weekday,@dt)+14 end
print @dt2
select cast(@dt2 as datetime)-----
2005-03-17