Get a list of dates between two dates using a func

Question

My question is similar to this MySQL question, but intended for SQL Server:

Is there a function or a query that will return a list of days between two dates? For example, lets say there is a function called ExplodeDates:

SELECT ExplodeDates('2010-01-01', '2010-01-13');

This would return a single column table with the values:

2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13

I'm thinking that a calendar/numbers table might be able to help me here.

---

Update

I decided to have a look at the three code answers provided, and the results of the execution - as a % of the total batch - are:

• Rob Farley's answer : 18%
• StingyJack's answer : 41%
• KM's answer : 41%

Lower is better

I have accepted Rob Farley's answer, as it was the fastest, even though numbers table solutions (used by both KM and StingyJack in their answers) are something of a favourite of mine. Rob Farley's was two-thirds faster.

Update 2

Alivia's answer is much more succinct. I have changed the accepted answer.

Answer 1

A few ideas:

If you need the list dates in order to loop through them, you could have a Start Date and Day Count parameters and do a while loop whilst creating the date and using it?

Use C# CLR Stored Procedures and write the code in C#

Do this outside the database in code

Answer 2

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

here is the function:

CREATE FUNCTION dbo.ListDates
(
     @StartDate    char(10)  
    ,@EndDate      char(10)
)
RETURNS
@DateList table
(
    Date datetime
)
AS
BEGIN


IF ISDATE(@StartDate)!=1 OR ISDATE(@EndDate)!=1
BEGIN
    RETURN
END

INSERT INTO @DateList
        (Date)
    SELECT
        CONVERT(datetime,@StartDate)+n.Number-1
        FROM Numbers  n
        WHERE Number<=DATEDIFF(day,@StartDate,CONVERT(datetime,@EndDate)+1)


RETURN

END --Function

use this:

select * from dbo.ListDates('2010-01-01', '2010-01-13')

output:

Date
-----------------------
2010-01-01 00:00:00.000
2010-01-02 00:00:00.000
2010-01-03 00:00:00.000
2010-01-04 00:00:00.000
2010-01-05 00:00:00.000
2010-01-06 00:00:00.000
2010-01-07 00:00:00.000
2010-01-08 00:00:00.000
2010-01-09 00:00:00.000
2010-01-10 00:00:00.000
2010-01-11 00:00:00.000
2010-01-12 00:00:00.000
2010-01-13 00:00:00.000

(13 row(s) affected)

Answer 3

this few lines are the simple answer for this question in sql server.

WITH mycte AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 < '2021-12-31'
)

SELECT  DateValue
FROM    mycte
OPTION (MAXRECURSION 0)

Answer 4

Perhaps if you wish to go an easier way, this should do it.

WITH date_range (calc_date) AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - 6, 0)
        UNION ALL SELECT DATEADD(DAY, 1, calc_date)
            FROM date_range
            WHERE DATEADD(DAY, 1, calc_date) < CURRENT_TIMESTAMP)
SELECT calc_date
FROM date_range;

But the temporary table is a very good approach also. Perhaps shall you also consider a populated calendar table.

Answer 5

This query works on Microsoft SQL Server.

select distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate
       from (
             SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v
             FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
                    (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
                  (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
       ) a
       where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)
       order by aDate asc;

Now let's look at how it works.

The inner query merely returns a list of integers from 0 to 9999. It will give us a range of 10,000 values for calculating dates. You can get more dates by adding rows for ten_thousands and hundred_thousands and so forth.

SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n as v
         FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
                (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
              (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
   ) a;

This part converts the string to a date and adds a number to it from the inner query.

cast('2010-01-01' as datetime) + ( a.v / 10 )

Then we convert the result into the format you want. This is also the column name!

format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' )

Next we extract only the distinct values and give the column name an alias of aDate.

distinct format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) as aDate

We use the where clause to filter in only dates within the range you want. Notice that we use the column name here since SQL Server does not accept the column alias, aDate, within the where clause.

where format( cast('2010-01-01' as datetime) + ( a.v / 10 ), 'yyyy-MM-dd' ) < cast('2010-01-13' as datetime)

Lastly, we sort the results.

   order by aDate asc;

Answer 6

In case you want to print years starting from a particular year till current date. Just altered the accepted answer.

WITH mycte AS
    (
      SELECT YEAR(CONVERT(DATE, '2006-01-01',102)) DateValue
      UNION ALL
      SELECT  DateValue + 1
      FROM    mycte   
      WHERE   DateValue + 1 < = YEAR(GETDATE())
    )
    SELECT  DateValue
    FROM    mycte

OPTION (MAXRECURSION 0)