怎么使用sql语句查询日期所在周的一周各天

检索日期所在周的一周各天日期方法 一、用到的函数有datepart(),dateadd() 1、datepart（)函数，返回代表指定日期的指定日期部分的整数。

语法：DATEPART ( datepart ,date ) 参数：datepart 是指定应返回的日期部分的参数。参数如下 2、DATEADD（) 函数在日期中添加或减去指定的时间间隔。

语法：DATEADD(datepart,number,date) date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

datepart 参数可以是下列的值： 二、以系统当前时间为例，检索一周各天时间的语句如下： 1、DATEPART(weekday,getdate（)）返回的是整型数值17，分别代表周日、周一到周六 2、语句分别获取周日到周六的日期时间，然后用union 进行检索结果连接。 3、已获取周日时间为例： DATEPART(weekday,getdate（)） 返回1，即当前日期就是周日，那么输出当前时间getdate(), DATEPART(weekday,getdate（)） 返回2，即前日期是周一，那么周日是前一天，使用函数获取前一天的日期：dateadd(dd,1,getdate（)） 以此类推就获取了日期所在周的周日日期时间。

select case when DATEPART(weekday,getdate())=1 then getdate() when DATEPART(weekday,getdate())=2 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,4,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,5,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,6,getdate()) end as ‘日期’，’周日’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,0,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,4,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,5,getdate()) end as ‘日期’，’周一’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,0,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,4,getdate()) end as ‘日期’，’周二’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,0,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,3,getdate()) end as ‘日期’，’周三’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,4,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,0,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,2,getdate()) end as ‘日期’，’周四’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,5,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,4,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,2,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,1,getdate()) when DATEPART(weekday,getdate())=6 then dateadd(dd,0,getdate()) when DATEPART(weekday,getdate())=7 then dateadd(dd,1,getdate()) end as ‘日期’，’周五’ unionselectcase when DATEPART(weekday,getdate())=1 then dateadd(dd,6,getdate()) when DATEPART(weekday,getdate())=2 then dateadd(dd,5,getdate()) when DATEPART(weekday,getdate())=3 then dateadd(dd,4,getdate()) when DATEPART(weekday,getdate())=4 then dateadd(dd,3,getdate()) when DATEPART(weekday,getdate())=5 then dateadd(dd,2,get。

sql 时间查询语句

给传递过来的时间值加个引号

单引号：

SELECT * FROM cocl WHERE riqi>'”&request(“kaishi”)&”‘ AND riqi<‘”&request(“jieshu”)&”‘ ORDER BY id DESC

双引号：

SELECT * FROM cocl WHERE riqi>”””&request(“kaishi”)&””” AND riqi<“””&request(“jieshu”)&””” ORDER BY id DESC

在知道里看引号有点不清楚，你复制到记事本里看好点

你的最终的SQL语句应该是

riqi>20010101

还是

riqi>’20010101′

如何查看sql语句执行时间

declare @sttime datetime

set @sttime=getdate()

print @sttime

Select * from case1219 把你的sql语句放在这里

SELECT RTRIM(CAST(DATEDIFF(MS, @sttime, GETDATE()) AS CHAR(10))) AS ‘TimeTaken’

或者这么写

DECLARE @t1 DATETIME;

DECLARE @t2 DATETIME;

SET @t1 = GETDATE();

Select * from dimDate ； 把你要查询的sql语句放在这里

SET @t2 = GETDATE();

SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;