CODEWITHSUNDEEP
sqlfunctiondate
Komsinica
Komsinica

Reputation: 23

SQL Function that creates a table of dates

This function returns a single date and not a list of 61 dates as expected. Any ideas what I am doing wrong? 

alter FUNCTION udf_foo()
RETURNS  @Result TABLE
(
days datetime
)

AS
begin
DECLARE @i int = -1
begin
WHILE @i < 60
SET @i = @i + 1
INSERT into @Result select  dateadd (day, -@i, getdate())
end ;
return;
end

Upvotes: 0

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can just use a CTE for this:

with dates as (
      select cast(getdate() as date) as dte
      union all
      select dateadd(day, -1, dte)
      from dates
      where dte >= dateadd(day, -60, getdate())
     )
select *
from dates;

You hardly need a function for this, although you could put this in a function if you really wanted to.

Here is an example of incorporating this into a view.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Like you wrote it, the body of the WHILE loop only spans the next statement.

You only get one result because the INSERT isn't part of the loop's body and it only executed once (after the loop incremented @i to 60).

If you want to have a body of more than one statement, enclose the statements in a BEGIN ... END block.

...
WHILE @i < 60
BEGIN
  SET @i = @i + 1;
  INSERT INTO @Result
              SELECT dateadd(day, -@i, getdate());
END;
...

Upvotes: 2

Related Questions