SQL Server : Table valued function returning dates for last 10 days from today

Question

I am new to SQL Server and am trying to write a table valued function that returns dates for the last 10 days from the current date. How do I do this?

Answer 1

Maybe this will take you a step closer. You can use below code to get last 10 dates + today. Tweak this and push this into a function.

SELECT 
    date = DATEADD(d,-1* days,CAST(GETDATE() AS DATE))
FROM
    (
    VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    )V(days)

Answer 2

First create a procedure that finds the last 10 days for a given input date like this:

CREATE PROCEDURE dbo.getLast10Days(@inputDate DATETIME)
AS BEGIN
DECLARE @count INT;
SET @count = 1;
WHILE @count <= 10
BEGIN
PRINT DATEADD(DAY, - @count, @inputDate)
SET @count = @count + 1
END
END
GO

In order to execute this procedure for the current date, this will do:

DECLARE @currentDate DATETIME
SET @currentDate = GETDATE()
EXEC dbo.getLast10Days @inputDate = @currentDate

If you don't want to use the current date as getdate(), but to use a date from a table, just do something like this:

DECLARE @currentDate DATETIME
SET @currentDate = SELECT theDateColumn FROM yourTable
EXEC dbo.getLast10Days @inputDate = @currentDate