时间戳

首页 > 解决方案 > 计算周名称的数量

sql - 计算周名称的数量

问题描述

我试图编辑，但我不知道周数：

declare @from datetime= '2019-06-01' declare @to datetime  = '2019-06-19' 

select datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI

查询工作得很好，但只是星期五，我也想编辑查询来计算：

Sunday
Monday
Tuesday
Wednesday
Thursday
Saturday

标签： sqlsql-server

解决方案

不知道你想要什么。它是两个日期之间的日期名称列表吗？

declare @from datetime= '2019-06-01' declare @to datetime  = '2019-06-19'

;WITH Tally (n) AS
(
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)
SELECT [DayName]= datename(weekday, @from), [Date]=@from
union all
select datename(weekday, dateadd(day, n, @from)), dateadd(day, n, @from)
FROM Tally
where n <= DATEDIFF(day, @from, @to)

输出

DayName     Date
Saturday    2019-06-01 00:00:00.000
Sunday      2019-06-02 00:00:00.000
Monday      2019-06-03 00:00:00.000
Tuesday     2019-06-04 00:00:00.000
Wednesday   2019-06-05 00:00:00.000
Thursday    2019-06-06 00:00:00.000
Friday      2019-06-07 00:00:00.000
Saturday    2019-06-08 00:00:00.000
Sunday      2019-06-09 00:00:00.000
Monday      2019-06-10 00:00:00.000
Tuesday     2019-06-11 00:00:00.000
Wednesday   2019-06-12 00:00:00.000
Thursday    2019-06-13 00:00:00.000
Friday      2019-06-14 00:00:00.000
Saturday    2019-06-15 00:00:00.000
Sunday      2019-06-16 00:00:00.000
Monday      2019-06-17 00:00:00.000
Tuesday     2019-06-18 00:00:00.000
Wednesday   2019-06-19 00:00:00.000

推荐阅读