Get a list of dates between two dates using a function

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

User · Answer

Try something like this   CREATE FUNCTION dbo ExplodeDates  startdate datetime   enddate datetime  returns table as return   with   N0 as  SELECT 1 as n UNION ALL SELECT 1   N1 as  SELECT 1 as n FROM N0 t1  N0 t2   N2 as  SELECT 1 as n FROM N1 t1  N1 t2   N3 as  SELECT 1 as n FROM N2 t1  N2 t2   N4 as  SELECT 1 as n FROM N3 t1  N3 t2   N5 as  SELECT 1 as n FROM N4 t1  N4 t2   N6 as  SELECT 1 as n FROM N5 t1  N5 t2   nums as  SELECT ROW NUMBER   OVER  ORDER BY  SELECT 1   as num FROM N6  SELECT DATEADD day num-1  startdate  as thedate FROM nums WHERE num  lt   DATEDIFF day  startdate  enddate    1      You then use   SELECT   FROM dbo ExplodeDates  20090401   20090531   as d    Edited  after the acceptance    Please note    if you already have a sufficiently large nums table then you should use   CREATE FUNCTION dbo ExplodeDates  startdate datetime   enddate datetime  returns table as return   SELECT DATEADD day num-1  startdate  as thedate FROM nums WHERE num  lt   DATEDIFF day  startdate  enddate    1      And you can create such a table using   CREATE TABLE dbo nums  num int PRIMARY KEY   INSERT dbo nums values  1   GO INSERT dbo nums SELECT num    SELECT COUNT    FROM nums  FROM nums GO 20   These lines will create a table of numbers containing 1M rows    and far quicker than inserting them one by one   You should NOT create your ExplodeDates function using a function that involves BEGIN and END  as the Query Optimizer becomes unable to simplify the query at all

User · Answer

A Bit late to the party  but I like this solution quite a bit   CREATE FUNCTION ExplodeDates  startDate DateTime   endDate DateTime  RETURNS table as return       SELECT  TOP  DATEDIFF DAY   startDate   endDate    1              DATEADD DAY  ROW NUMBER   OVER ORDER BY a object id  - 1   startDate  AS DATE     FROM    sys all objects a             CROSS JOIN sys all objects b

User · Answer

This does exactly what you want  modified from Will s earlier post   No need for helper tables or loops   WITH date range  calc date  AS       SELECT DATEADD DAY  DATEDIFF DAY  0   2010-01-13   - DATEDIFF DAY   2010-01-01    2010-01-13    0          UNION ALL SELECT DATEADD DAY  1  calc date              FROM date range             WHERE DATEADD DAY  1  calc date   lt    2010-01-13   SELECT calc date FROM date range

User · Answer

All you have to do is just change the hard coded value in the code provided below  DECLARE  firstDate datetime     DECLARE  secondDate datetime     DECLARE  totalDays  INT     SELECT  firstDate   getDate   - 30     SELECT  secondDate   getDate        DECLARE  index INT     SELECT  index   0     SELECT  totalDays   datediff day   firstDate   secondDate       CREATE TABLE  temp                ID INT NOT NULL IDENTITY 1 1           CommonDate DATETIME NULL            WHILE  index  lt   totalDays         BEGIN              INSERT INTO  temp  CommonDate  VALUES   DATEADD Day   index   firstDate                  SELECT  index    index   1         END      SELECT CONVERT VARCHAR 10   CommonDate  102  as  Date Between  FROM  temp      DROP TABLE  temp

User · Answer

WITH TEMP  DIA  SIGUIENTE DIA   AS             SELECT                 1                  CAST  FECHAINI AS DATE              FROM                 DUAL            UNION ALL             SELECT                 DIA                  DATEADD DAY  DIA  SIGUIENTE DIA              FROM                 TEMP             WHERE                DIA  lt  DATEDIFF DAY    FECHAINI   FECHAFIN                    AND DATEADD DAY  1  SIGUIENTE DIA   lt    CAST  FECHAFIN AS DATE                          SELECT                SIGUIENTE DIA AS CALENDARIO             FROM               TEMP            ORDER BY                  SIGUIENTE DIA   The detail is on the table DUAL  but if your exchange  this table for a dummy table this works

User · Answer

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  lt   2021-12-31     SELECT  DateValue FROM    mycte OPTION  MAXRECURSION 0

User · Answer

Declare  date1 date    2016-01-01                  date2 date    2016-03-31                  date index date Declare  calender table  D date  SET  date index    date1 WHILE  date index lt   date2 BEGIN INSERT INTO  calender SELECT  date index  SET  date index   dateadd day 1  date index   IF  date index gt  date2 Break ELSE Continue END

User · Answer

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   lt  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

User · Answer

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

User · Answer

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     lt  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     lt  cast  2010-01-13  as datetime    Lastly  we sort the results      order by aDate asc

User · Answer

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 lt 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 lt  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

User · Answer

DECLARE  StartDate DATE    2017-09-13            EndDate DATE    2017-09-16   SELECT date  FROM     SELECT DATE   DATEADD DAY  rn - 1   StartDate    FROM          SELECT TOP  DATEDIFF DAY   StartDate  DATEADD DAY 1  EndDate          rn   ROW NUMBER   OVER  ORDER BY s1  object id       FROM sys all objects AS s1     CROSS JOIN sys all objects AS s2     ORDER BY s1  object id      AS x   AS y   Result   2017-09-13  2017-09-14  2017-09-15  2017-09-16

User · Answer

if you re in a situation like me where procedures and functions are prohibited  and your sql user does not have permissions for insert  therefore insert not allowed  also  set declare temporary variables like  c is not allowed   but you want to generate a list of dates in a specific period  say current year to do some aggregation  use this  select   from   select adddate  1970-01-01  t4 10000   t3 1000   t2 100   t1 10   t0  gen date from   select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  t0    select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  t1    select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  t2    select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  t3    select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9  t4  v where gen date between  2017-01-01  and  2017-12-31

User · Answer

DECLARE  MinDate DATETIME    2012-09-23 00 02 00 000        MaxDate DATETIME    2012-09-25 00 00 00 000    SELECT  TOP  DATEDIFF DAY   MinDate   MaxDate    1  Dates   DATEADD DAY  ROW NUMBER   OVER ORDER BY a object id  - 1   MinDate  FROM sys all objects a CROSS JOIN sys all objects b

User · Answer

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  lt    YEAR GETDATE              SELECT  DateValue     FROM    mycte  OPTION  MAXRECURSION 0

User · Answer

Answer is avialbe here How to list all dates between two dates  Create Procedure SelectDates  fromDate Date   toDate Date  AS BEGIN     SELECT DATEADD DAY number  fromDate   Date      FROM master  spt values     WHERE type    P      AND DATEADD DAY number  fromDate   lt   toDate  END

User · Answer

I m an oracle guy  but I believe MS SQL Server has support for the connect by clause   select  sysdate   level from    dual connect by level  lt   10     The output is   SYSDATE LEVEL 05-SEP-09 06-SEP-09 07-SEP-09 08-SEP-09 09-SEP-09 10-SEP-09 11-SEP-09 12-SEP-09 13-SEP-09 14-SEP-09   Dual is just a  dummy  table that comes with oracle  it contains 1 row and the word  dummy  as the value of the single column

User · Answer

SELECT  dateadd dd DAYS  2013-09-07 00 00 00   DATES INTO         TEMP1 FROM  SELECT TOP 365 colorder - 1 AS DAYS from master  syscolumns      WHERE id   -519536829 order by colorder  a  WHERE datediff dd dateadd dd DAYS  2013-09-07 00 00 00    2013-09-13 00 00 00     gt   0      AND  dateadd dd DAYS  2013-09-07 00 00 00    lt     2013-09-13 00 00 00        SELECT   FROM  TEMP1

User · Answer

Would all these dates be in the database already or do you just want to know the days between the two dates  If it s the first you could use the BETWEEN or  lt      to find the dates between  EXAMPLE    SELECT column name s  FROM table name WHERE column name BETWEEN value1 AND value2   OR  SELECT column name s  FROM table name WHERE column name value1  gt   column name AND column name   lt  value2

User · Answer

--     Six of one half dozen of another   Another method assuming MsSql   Declare  MonthStart    datetime     convert DateTime  07 01 2016   Declare  MonthEnd      datetime     convert DateTime  07 31 2016   Declare  DayCount int       Int     0  Declare  WhileCount int     Int     0  set  DayCount int   DATEDIFF DAY   MonthStart   MonthEnd  select  WhileCount int WHILE  WhileCount int  lt   DayCount int   1 BEGIN    print convert Varchar 24  DateAdd day  WhileCount int  MonthStart  101     SET  WhileCount int    WhileCount int   1  END

User · Answer

Definately a numbers table  though tyou may want to use Mark Redman s idea of a CLR proc assembly if you really need the performance    How to create the table of dates  and a super fast way to create a numbers table     Gets a list of integers into a temp table  Jeff Moden s idea from SqlServerCentral com     SELECT TOP 10950   30 years of days           IDENTITY INT 1 1  as N    INTO  Numbers    FROM Master dbo SysColumns sc1          Master dbo SysColumns sc2     Create the dates table   CREATE TABLE  TableOfDates        fld date   datetime  NOT NULL   CONSTRAINT  PK TableOfDates  PRIMARY KEY CLUSTERED         fld date  ASC  WITH FILLFACTOR   99 ON  PRIMARY    ON  PRIMARY     fill the table with dates   DECLARE  daysFromFirstDateInTheTable int DECLARE  firstDateInTheTable DATETIME  SET  firstDateInTheTable    01 01 1998  SET  daysFromFirstDateInTheTable    SELECT  DATEDIFF dd   firstDateInTheTable  GETDATE      1    INSERT INTO       TableOfDates SELECT        DATEADD dd nums n -  daysFromFirstDateInTheTable  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime   as FLD Date FROM  Numbers nums   Now that you have a table of dates  you can use a function  NOT A PROC  like KM s to get the table of them    CREATE FUNCTION dbo ListDates         StartDate    DATETIME         EndDate      DATETIME   RETURNS  DateList table       Date datetime   AS BEGIN    add some validation logic of your own to make sure that the inputs are sound Adjust the rest as needed      INSERT INTO      DateList   SELECT FLD Date FROM TableOfDates  NOLOCK  WHERE FLD Date  gt    StartDate AND FLD Date  lt    EndDate   RETURN END

[sql-server] Get a list of dates between two dates using a function

Examples related to sql-server

Examples related to date