Return next 10 days including weekday name

Question

Looking for a query or function which, for a given date, will return next 10 days (including the given date as a parameter) as two columns: - date - weekday name

I am using SQL Server 2019.

UPDATE (@Dale K): I have calculated dates and weekdays, but those are as two separate queries and in new row instead of multiple rows

declare @mydate datetime
set @mydate = '2020-1-1'

select @mydate + 0, @mydate + 1, @mydate + 2, @mydate + 3, @mydate + 4, @mydate + 5, @mydate + 6

select
datename(dw, @mydate + 0),
datename(dw, @mydate + 1),
datename(dw, @mydate + 2),
datename(dw, @mydate + 3),
datename(dw, @mydate + 4),
datename(dw, @mydate + 5),
datename(dw, @mydate + 6)

We'd love to help you, help, not do it all. Have a go, show us where you got stuck... then we'll help. — Dale K, Jan 16 '20 at 00:15
@DaleK I have shared what I have done, however doubtful if this is of any help for other guys seeking for solution, not for those who might answer. That was the original reason for not providing it. — Megrez7, Jan 16 '20 at 00:31
@Magrez7 its always helpful to see what you have tried, it shows a) You have tried, b) Your level of SQL knowledge, c) Your logic, b) How you think it might be solved. — Dale K, Jan 16 '20 at 00:34

score 6 · Accepted Answer · answered Jan 16 '20 at 00:19

If you always need 10 days, you can use a function such as this:

CREATE FUNCTION dbo.GetNext10Days(@FromDate DATE)
RETURNS TABLE AS RETURN
SELECT DATEADD(DAY,n,@FromDate) AS TheDate,
       DATENAME(WEEKDAY,DATEADD(DAY,n,@FromDate)) AS WeekDayName
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)

GO
SELECT * FROM dbo.GetNext10Days(GETDATE())

If you might need a different number of days, you can create a function that returns a table of numbers and call that function instead of the VALUES clause (to generate the numbers, I prefer the method described in this answer: https://stackoverflow.com/a/1394239/1187211).

score 1 · Answer 2 · answered Jan 16 '20 at 00:21

you can use dateadd and datename(weekday,date) function to do it.

create function dbo.getnext10days(@fromdate date)
returns table as return
with cte  as (
    select 1 val,dateadd(day,1,@fromdate) [date]
    union all
    select t2.val +1 as val,dateadd(day,t2.val+1,@fromdate) from (select 1 val) t1 
    inner join cte t2 on t2.val < 10 
)
select 
    [date],datename(weekday,[date])  as [weekday name]
from cte;

online demo link

score -1 · Answer 3 · answered Jan 16 '20 at 05:08

You can also use the inbuilt spt_values to generate the dates as shown below.

SELECT 
    DATEADD(DAY,number+1, getdate()) [Date], 
    datename(weekday, DATEADD(DAY,number+1, getdate())) [Week Day]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1, getdate()) < DATEADD(day, 10, getdate())

Here is the db<>fiddle demo.

Return next 10 days including weekday name

3 Answers3