如何用sql获取当前周的7个时间---在线等!---多谢!
例如:今天是2007-4-25,当前周的时间就是(2007-4-22,2007-4-23,...2007-4-28)7个时间。
查询当前周:
select * from table
where startdate in(2007-4-22,2007-4-23,...2007-4-28)
查询下一周(也就是当前周的下一周):
select * from table
where startdate in(2007-4-29,2007-4-30,...2007-5-5)
查询上一周(也就是当前周的上一周):
select * from table
where startdate in(2007-4-15,2007-4-16,...2007-4-21)
------解决方案--------------------select DATEADD(wk,DATEDIFF(wk,0,getdate()),0),DATEADD(wk,DATEDIFF(wk,0,getdate()),6)
能得到一周的第一天和最后一天,你用 between ...and ..
------解决方案--------------------查询当前周:
select * from table
where datediff(wk,startdate,getdate())=0
查询下一周(也就是当前周的下一周):
select * from table
where datediff(wk,startdate,getdate())=-1
查询上一周(也就是当前周的上一周):
select * from table
where datediff(wk,startdate,getdate())=1
------解决方案--------------------用DATEDIFF
------解决方案--------------------between DATEADD(wk,DATEDIFF(wk,0,getdate())+1,0) and DATEADD(wk,DATEDIFF(wk,0,getdate())+1,6) ---下一周
between DATEADD(wk,DATEDIFF(wk,0,getdate())-1,0) and DATEADD(wk,DATEDIFF(wk,0,getdate())-1,6) ---上一周
------解决方案--------------------我觉得可能这样效率会更高:
declare @today datetime
select @today = convert(varchar(10), getdate(), 20)
set @today = '2007-04-22 '
declare @weekday int
select @weekday = datepart(weekday, @today)
declare @st datetime, @et datetime
select @st = dateadd(dd, 1-@weekday, @today), @et = dateadd(dd, 7-@weekday, @today)
select * from table
where startdate between @st and @et
当然,这里的开始时间、结束时间跟你sql server的设置有关,即你的周是从周一开始还是从周日开始。
------解决方案--------------------我觉得可能这样效率会更高:
declare @today datetime
select @today = convert(varchar(10), getdate(), 20)
declare @weekday int
select @weekday = datepart(weekday, @today)
declare @st datetime, @et datetime
select @st = dateadd(dd, 1-@weekday, @today), @et = dateadd(dd, 7-@weekday, @today)
select * from table
where startdate between @st and @et
当然,这里的开始时间、结束时间跟你sql server的设置有关,即你的周是从周一开始还是从周日开始。
------解决方案--------------------up
------解决方案--------------------ljsql() ( ) 信誉:100 Blog 加为好友 2007-04-25 10:52:12 得分: 0
..这个0是1900年的意思...学习
0这是SQL默认的初始时间为1900-01-01