Skip to content
Advertisement

Calculate the numbers of week names

I tried to edit but I have not knowledge about the number of weeks:

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 

The query work very good but it is just Friday, I want to edit the query to calculate too:

Sunday
Monday
Tuesday
Wednesday
Thursday
Saturday

Advertisement

Answer

not sure what you want here. is it a list of day names between two dates?

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)

output

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement