怎么使用sql语句查询日期所在周的一周各天
发布网友
发布时间：2022-04-25 17:09
共1个回答
热心网友
时间：2022-04-07 21:25
检索日期所在周的一周各天日期方法
一、用到的函数有datepart(),dateadd()
1、datepart()函数，返回代表指定日期的指定日期部分的整数。
语法：DATEPART ( datepart ,date )
参数：datepart
是指定应返回的日期部分的参数。参数如下
2、DATEADD() 函数在日期中添加或减去指定的时间间隔。
语法：DATEADD(datepart,number,date)
date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
datepart 参数可以是下列的值：
二、以系统当前时间为例，检索一周各天时间的语句如下：
1、DATEPART(weekday,getdate())返回的是整型数值1-7，分别代表周日、周一到周六
2、语句分别获取周日到周六的日期时间，然后用union 进行检索结果连接。
3、已获取周日时间为例：
DATEPART(weekday,getdate()) 返回1，即当前日期就是周日，那么输出当前时间getdate()，
DATEPART(weekday,getdate()) 返回2，即前日期是周一，那么周日是前一天，使用函数获取前一天的日期：dateadd(dd,-1,getdate())
以此类推就获取了日期所在周的周日日期时间。
selectcase 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 '日期','周日' union
select
case 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 '日期','周一' union
select
case 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 '日期','周二' union
select
case 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 '日期','周三' union
select
case 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 '日期','周四' union
select
case 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 '日期','周五' union
select
case 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,getdate())
when DATEPART(weekday,getdate())=6 then dateadd(dd,1,getdate())
when DATEPART(weekday,getdate())=7 then dateadd(dd,0,getdate()) end as '日期','周六'
三、执行结果
