declare @t datetime --输入日期
declare @i intset @t = cast('2004-01-05' as datetime)
set @i = (@@Datefirst + datepart(weekday,@t))% 7select case
when @i = 0 then @t - 12
when @i = 1 then @t - 13
when @i = 2 then @t - 7
when @i = 3 then @t - 8
when @i = 4 then @t - 9
when @i = 5 then @t - 10
when @i = 6 then @t - 11
end as Mon,
case
when @i = 0 then @t - 6
when @i = 1 then @t - 7
when @i = 2 then @t - 1
when @i = 3 then @t - 2
when @i = 4 then @t - 3
when @i = 5 then @t - 4
when @i = 6 then @t - 5
end as Sun
declare @i intset @t = cast('2004-01-05' as datetime)
set @i = (@@Datefirst + datepart(weekday,@t))% 7select case
when @i = 0 then @t - 12
when @i = 1 then @t - 13
when @i = 2 then @t - 7
when @i = 3 then @t - 8
when @i = 4 then @t - 9
when @i = 5 then @t - 10
when @i = 6 then @t - 11
end as Mon,
case
when @i = 0 then @t - 6
when @i = 1 then @t - 7
when @i = 2 then @t - 1
when @i = 3 then @t - 2
when @i = 4 then @t - 3
when @i = 5 then @t - 4
when @i = 6 then @t - 5
end as Sun
解决方案 »
- 本机安装sql server ,别的计算如何访问
- 一个字段如何分多个列来显示?
- 重发
- 多行转单行
- [^_^]大家来帮忙,SQL SERVER2000转到2005的问题!
- 我有一个系统,包住这两个文件mydatabase_Data.MDF和mydatabase_Log.LDF,我现在想在SQL2000里创建这系统的后台数据库,请问如何导入这两
- SQLServer2005数据库数据
- 如何确定字段的第一个字符是不是数字
- 能不能把排序时取头几位那个“几”作为变量呢?
- 谁能搞定这个将EXCEL的数据导入SQL SERVER时的报错问题?百万火急!
- 无法验证SQL密码??
- 关于ADO的recordcount=-1的问题
用这两个函数应该可以写个符合你的条件的东西的,用weekday应该就可以了
判断一下日期是周几
declare @i intset @t = cast('2005-01-17' as datetime)
set @i = (@@Datefirst + datepart(weekday,@t))% 7select case
when @i = 0 then @t - 12
when @i = 1 then @t - 13
when @i = 2 then @t - 7
when @i = 3 then @t - 8
when @i = 4 then @t - 9
when @i = 5 then @t - 10
when @i = 6 then @t - 11
end as Mon,
case
when @i = 0 then @t - 6
when @i = 1 then @t - 7
when @i = 2 then @t - 1
when @i = 3 then @t - 2
when @i = 4 then @t - 3
when @i = 5 then @t - 4
when @i = 6 then @t - 5
end as Sun
--输出
Mon Sun
----------------------------------------------
2005-01-10 00:00:00 2005-01-16 00:00:00
select @today = datepart(dw,getdate())--上个星期天
select convert(varchar,dateadd(dy,-@today,getdate()),102)
--上个星期一
select convert(varchar,dateadd(dy,@today-8,getdate()),102)
set @d=getdate()
select 上周日=case when datepart(weekday,@d)=1
then @d-datepart(weekday,@d)+1-7
else @d-datepart(weekday,@d)+1
end,
上周一=case when datepart(weekday,@d)=1
then @d-datepart(weekday,@d)-12
else @d-datepart(weekday,@d)-5
end
select @var = @@DATEFIRST
set DATEFIRST 1上周日
select convert(char(10),dateadd(dd, -(datepart(weekday, getdate())), getdate()), 120)上周一
select convert(char(10),dateadd(dd, -(datepart(weekday, getdate()))-6, getdate()), 120)set DATEFIRST @var
declare @i intset @t = cast('2005-01-17' as datetime)
set @i = (@@Datefirst + datepart(weekday,@t))% 7select case
when @i = 0 then @t - 12
when @i = 1 then @t - 13
when @i = 2 then @t - 7
when @i = 3 then @t - 8
when @i = 4 then @t - 9
when @i = 5 then @t - 10
when @i = 6 then @t - 11
end as Mon,
case
when @i = 0 then @t - 6
when @i = 1 then @t - 7
when @i = 2 then @t - 1
when @i = 3 then @t - 2
when @i = 4 then @t - 3
when @i = 5 then @t - 4
when @i = 6 then @t - 5
end as Sun
set @d='2005-01-17'
select dateadd(day,- 5-DATEPART(dw, @d) ,@d) as 上周一,
dateadd(day,1-DATEPART(dw, @d),@d) as 上周日上周一 上周日
--------------------------- -----------------------------
2005-01-10 00:00:00.000 2005-01-16 00:00:00.000(所影响的行数为 1 行)
set @ = '2004-1-10'select
dateadd(day,case when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 = 0 --周六
then -5
when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 = 1 --周日(七)
then -6
when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 between 2 and 6 --周一至周五
then 2 - (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7
end
,dateadd(week,-1,@)) as 上周一
,dateadd(day,case when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 = 0 --周六
then 1
when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 = 1 --周日(七)
then 0
when (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7 between 2 and 6 --周一至周五
then 8 - (@@Datefirst + datepart(weekday,dateadd(week,-1,@))) % 7
end
,dateadd(week,-1,@)) as 上周日
set @ = getdate()select @ +
case when (@@Datefirst + datepart(weekday,@)) % 7 = 0 --周六
then -5
when (@@Datefirst + datepart(weekday,@)) % 7 = 1 --周日(七)
then -6
when (@@Datefirst + datepart(weekday,@)) % 7 between 2 and 6 --周一至周五
then 2 - (@@Datefirst + datepart(weekday,@)) % 7
end + N.i
from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7
) N