请教条关于时间的查询语句

Question

select datediff("d",'2014-12-5 7:00:00','2014-12-12 7:00:00')两个日期之间是七天

Answer 1

--上一周
SELECT *
FROM TB
WHERE DATEPART(WEEK,DATEADD(HOUR,-31,日期))
=DATEPART(WEEK,DATEADD(HOUR,-31,GETDATE()))-1--本周
SELECT *
FROM TB
WHERE DATEPART(WEEK,DATEADD(HOUR,-31,日期))
=DATEPART(WEEK,DATEADD(HOUR,-31,GETDATE()))

Answer 2

DECLARE @old_DATEFIRST int
SET @old_DATEFIRST = @@DATEFIRST
SET DATEFIRST 1-- 因为有跨年的情况，比较周数不正确，应该求周的开始、结束时间。
DECLARE @week1 datetime
DECLARE @week2 datetime-- 求本周一
SET @week1 = DateAdd(day,
                     1-DatePart(weekday,GetDate()),
                     GetDate())
-- 划分时间用 07:00
SET @week1 = Convert(datetime,
                     Convert(varchar(10),@week1,120)+' 07:00',
                     120)
/* 如果是查询上周就减7天
SET @week1 = DateAdd(day,-7,@week1)*/
SET @week2 = DateAdd(day,7,@week1)
--SELECT @week1, @week2;with table1(编号,日期) AS (
    SELECT 1,'2015-01-05 06:59:59' UNION ALL
    SELECT 2,'2015-01-05 07:00:00' UNION ALL
    SELECT 3,'2015-01-12 06:59:59' UNION ALL
    SELECT 4,'2015-01-12 07:00:00' 
)
SELECT *
  FROM table1
 WHERE 日期 >= @week1
   AND 日期 < @week2SET DATEFIRST @old_DATEFIRST
本周
         编号 日期
----------- -------------------
          4 2015-01-12 07:00:00
上周
         编号 日期
----------- -------------------
          2 2015-01-05 07:00:00
          3 2015-01-12 06:59:59