generate days from date range

Question

I would like to run a query like  select     as days where  date  is between  2010-01-20  and  2010-01-24    And return data like      days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24

User · Answer

try this   SELECT TO DATE  20160210   yyyymmdd   - 1   LEVEL AS start day  from DUAL connect by level  lt    TO DATE  20160228   yyyymmdd     1  - TO DATE  20160210   yyyymmdd

User · Answer

Here is another variation using views   CREATE VIEW digits AS   SELECT 0 AS digit UNION ALL   SELECT 1 UNION ALL   SELECT 2 UNION ALL   SELECT 3 UNION ALL   SELECT 4 UNION ALL   SELECT 5 UNION ALL   SELECT 6 UNION ALL   SELECT 7 UNION ALL   SELECT 8 UNION ALL   SELECT 9   CREATE VIEW numbers AS   SELECT     ones digit   tens digit   10   hundreds digit   100   thousands digit   1000 AS number   FROM     digits as ones      digits as tens      digits as hundreds      digits as thousands   CREATE VIEW dates AS   SELECT     SUBDATE CURRENT DATE    number  AS date   FROM     numbers    And then you can simply do  see how elegant it is     SELECT   date FROM   dates WHERE   date BETWEEN  2010-01-20  AND  2010-01-24  ORDER BY   date   Update  It is worth noting that you will only be able to generate past dates starting from the current date  If you want to generate any kind of dates range  past  future  and in between   you will have to use this view instead   CREATE VIEW dates AS   SELECT     SUBDATE CURRENT DATE    number  AS date   FROM     numbers   UNION ALL   SELECT     ADDDATE CURRENT DATE    number   1  AS date   FROM     numbers

User · Answer

if you will ever need more then a couple days  you need a table   Create a date range in mysql  then    select from days day  count mytable field  as fields from days left join mytable on day date where date between x and y

User · Answer

Elegant solution using new recursive  Common Table Expressions  functionality in MariaDB    10 3 and MySQL    8 0   WITH RECURSIVE t as       select  2019-01-01  as dt   UNION     SELECT DATE ADD t dt  INTERVAL 1 DAY  FROM t WHERE DATE ADD t dt  INTERVAL 1 DAY   lt    2019-04-30    select   FROM t    The above returns a table of dates between  2019-01-01  and  2019-04-30   It is also decently fast  Returning 1000 years worth of dates   365 000 days  takes about 400ms on my machine

User · Answer

SQLite version of RedFilters top solution  select d Date from       select      date julianday  2010-01-20      a a    10   b a     100   c a    as Date     from  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as a     cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as b     cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as c   d where  d Date between  2010-01-20  and  2010-01-24   order by d Date

User · Answer

A more generic answer that works in AWS MySQL   select datetable Date from       select date format adddate now   - a a    10   b a     100   c a      Y- m- d   AS Date     from  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as a      cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as b      cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as c   datetable where datetable Date between now   - INTERVAL 14 Day and Now   order by datetable Date DESC

User · Answer

Generate dates between two date fields  If you are aware with SQL CTE query  then this solution will helps you to solve your question  Here is example   We have dates in one table  Table Name     testdate     STARTDATE   ENDDATE 10 24 2012  10 24 2012 10 27 2012  10 29 2012 10 30 2012  10 30 2012   Require Result    STARTDATE 10 24 2012 10 27 2012 10 28 2012 10 29 2012 10 30 2012   Solution   WITH CTE AS    SELECT DISTINCT convert varchar 10  StartTime  101  AS StartTime                     datediff dd StartTime  endTime  AS diff    FROM dbo testdate    UNION ALL SELECT StartTime                      diff - 1 AS diff    FROM CTE    WHERE diff lt  gt  0  SELECT DISTINCT DateAdd dd diff  StartTime  AS StartTime FROM CTE   Explanation  CTE Recursive query explanation   First part of query  SELECT DISTINCT convert varchar 10   StartTime  101  AS StartTime  datediff dd  StartTime  endTime  AS diff FROM dbo testdate   Explanation  firstcolumn is    startdate     second column is difference of start and end date in days and it will be consider as    diff    column Second part of query  UNION ALL SELECT StartTime  diff-1 AS diff FROM CTE WHERE diff lt  gt 0  Explanation  Union all will inherit result of above query until result goes null   So    StartTime    result is inherit from generated CTE query  and from diff  decrease -   1     so its looks like 3  2  and 1 until 0   For example  STARTDATE   DIFF 10 24 2012  0 10 27 2012  0 10 27 2012  1 10 27 2012  2 10 30 2012  0   Result Specification  STARTDATE       Specification 10 24 2012  -- gt  From Record 1 10 27 2012  -- gt  From Record 2 10 27 2012  -- gt  From Record 2 10 27 2012  -- gt  From Record 2 10 30 2012  -- gt  From Record 3    3rd Part of Query  SELECT DISTINCT DateAdd dd diff  StartTime  AS StartTime FROM CTE  It will add day    diff    in    startdate    so result should be as below   Result   STARTDATE 10 24 2012 10 27 2012 10 28 2012 10 29 2012 10 30 2012

User · Answer

if you want the list of dates between two dates   create table  dates   date  smalldatetime  while  since  lt   to begin      insert into  dates dateadd day 1  since        set  since   dateadd day 1  since  end select  date  from  dates    fiddle here  http   sqlfiddle com   6 9eecb 3469

User · Answer

thx Pentium10 - you made me join stackoverflow    -  this is my porting to msaccess - think it ll work on any version   SELECT date value FROM  SELECT a espr1  10 b espr1   100 c espr1  AS integer value  dateadd  d  integer value dateserial  start year    start month    start day    as date value FROM  select   from            select top 1  0  as espr1 from MSysObjects     union all     select top 1  1  as espr2 from MSysObjects     union all     select top 1  2  as espr3 from MSysObjects     union all     select top 1  3  as espr4 from MSysObjects     union all     select top 1  4  as espr5 from MSysObjects     union all     select top 1  5  as espr6 from MSysObjects     union all     select top 1  6  as espr7 from MSysObjects     union all     select top 1  7  as espr8 from MSysObjects     union all     select top 1  8  as espr9 from MSysObjects     union all     select top 1  9  as espr9 from MSysObjects       as a            select top 1  0  as espr1 from MSysObjects     union all     select top 1  1  as espr2 from MSysObjects     union all     select top 1  2  as espr3 from MSysObjects     union all     select top 1  3  as espr4 from MSysObjects     union all     select top 1  4  as espr5 from MSysObjects     union all     select top 1  5  as espr6 from MSysObjects     union all     select top 1  6  as espr7 from MSysObjects     union all     select top 1  7  as espr8 from MSysObjects     union all     select top 1  8  as espr9 from MSysObjects     union all     select top 1  9  as espr9 from MSysObjects       as b            select top 1  0  as espr1 from MSysObjects     union all     select top 1  1  as espr2 from MSysObjects     union all     select top 1  2  as espr3 from MSysObjects     union all     select top 1  3  as espr4 from MSysObjects     union all     select top 1  4  as espr5 from MSysObjects     union all     select top 1  5  as espr6 from MSysObjects     union all     select top 1  6  as espr7 from MSysObjects     union all     select top 1  7  as espr8 from MSysObjects     union all     select top 1  8  as espr9 from MSysObjects     union all     select top 1  9  as espr9 from MSysObjects       as c       as d   WHERE date value  between dateserial  start year    start month    start day    and dateserial  end year    end month    end day      referenced MSysObjects just  cause access need a table countin  at least 1 record  in a from clause - any table with at least 1 record would do

User · Answer

WITH   Digits AS  SELECT 0 D 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     Dates AS  SELECT adddate  1970-01-01  t4 d 10000   t3 d 1000   t2 d 100   t1 d 10  t0 d  AS date FROM Digits AS t0  Digits AS t1  Digits AS t2  Digits AS t3  Digits AS t4  SELECT   FROM Dates WHERE date BETWEEN  2017-01-01  AND  2017-12-31

User · Answer

set language   SPANISH  DECLARE  table table fechaDesde datetime   fechaHasta datetime    INSERT  table VALUES  20151231     20161231    WITH x AS                SELECT   DATEADD  m   1  fechaDesde   as fecha  FROM  table         UNION ALL         SELECT  DATEADD  m   1  fecha           FROM  table t INNER JOIN x ON  DATEADD  m   1  x fecha    lt   t fechaHasta       SELECT LEFT  CONVERT  VARCHAR  fecha   112     6   as Periodo Id   DATEPART   dd  DATEADD dd - DAY fecha -1  fecha   Num Dia Inicio  DATEADD dd - DAY fecha -1  fecha  Fecha Inicio  DATEPART   mm   fecha   Mes Id  DATEPART   yy   fecha   Anio  DATEPART   dd  DATEADD dd - DAY DATEADD mm 1 fecha    DATEADD mm 1 fecha    Num Dia Fin  DATEADD dd - DAY DATEADD mm 1 fecha    DATEADD mm 1 fecha   ultimoDia  datename MONTH  fecha  mes   Q    convert varchar 10    DATEPART QUARTER  fecha   Trimestre Name FROM x  OPTION MAXRECURSION 0

User · Answer

DELIMITER    CREATE PROCEDURE GenerateRangeDates IN dateStart DATE  IN dateEnd DATE  BEGIN      CREATE TEMPORARY TABLE IF NOT EXISTS dates  day DATE        loopDate  LOOP         INSERT INTO dates day  VALUES  dateStart            SET dateStart   DATE ADD dateStart  INTERVAL 1 DAY            IF dateStart  lt   dateEnd              THEN ITERATE loopDate              ELSE LEAVE loopDate          END IF      END LOOP loopDate       SELECT day FROM dates      DROP TEMPORARY TABLE IF EXISTS dates   END      -- Call procedure call GenerateRangeDates           now   - INTERVAL 40 DAY          now

User · Answer

Accepted answer didn t work for PostgreSQL  syntax error at or near  a     The way you do this in PostgreSQL is by using generate series function  i e    SELECT day  date FROM generate series  2010-01-20    2010-01-24   INTERVAL  1 day   day       day ------------  2010-01-20  2010-01-21  2010-01-22  2010-01-23  2010-01-24  5 rows

User · Answer

For Access 2010 - multiple steps required  I followed the same pattern as posted above  but thought I could help someone in Access   Worked great for me  I didn t have to keep a seeded table of dates   Create a table called DUAL  similar to how the Oracle DUAL table works    ID  AutoNumber  DummyColumn  Text  Add one row values  1  DummyRow     Create a query named  ZeroThru9Q   manually enter the following syntax   SELECT 0 AS a FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual UNION ALL SELECT 6 FROM dual UNION ALL SELECT 7 FROM dual UNION ALL SELECT 8 FROM dual UNION ALL SELECT 9 FROM dual    Create a query named  TodayMinus1KQ   for dates before today   manually enter the following syntax   SELECT date   -  a a    10   b a     100   c a   AS MyDate FROM    SELECT      FROM ZeroThru9Q  AS a      SELECT      FROM ZeroThru9Q  AS b      SELECT      FROM ZeroThru9Q  AS c   Create a query named  TodayPlus1KQ   for dates after today   manually enter the following syntax   SELECT date      a a    10   b a     100   c a   AS MyDate FROM    SELECT      FROM ZeroThru9Q  AS a      SELECT      FROM ZeroThru9Q  AS b      SELECT      FROM ZeroThru9Q  AS c    Create a union query named  TodayPlusMinus1KQ   for dates   - 1000 days    SELECT MyDate FROM TodayMinus1KQ UNION SELECT MyDate FROM TodayPlus1KQ    Now you can use the query   SELECT MyDate FROM TodayPlusMinus1KQ WHERE MyDate BETWEEN  05 01 2014  and  05 30 2014

User · Answer

Procedure   temporary table   DELIMITER     CREATE DEFINER  root   localhost  PROCEDURE  days  IN dateStart DATE  IN dateEnd DATE  BEGIN      CREATE TEMPORARY TABLE IF NOT EXISTS date range  day DATE        WHILE dateStart  lt   dateEnd DO       INSERT INTO date range VALUES  dateStart         SET dateStart   DATE ADD dateStart  INTERVAL 1 DAY       END WHILE       SELECT   FROM date range      DROP TEMPORARY TABLE IF EXISTS date range   END

User · Answer

For anyone who wants this as a saved view  MySQL doesn t support nested select statements in views    create view zero to nine as     select 0 as n union all      select 1 union all      select 2 union all      select 3 union all      select 4 union all      select 5 union all      select 6 union all      select 7 union all      select 8 union all      select 9   create view date range as     select curdate   - INTERVAL  a n    10   b n     100   c n   DAY as date     from zero to nine as a     cross join zero to nine as b     cross join zero to nine as c    You can then do  select   from date range   to get  date --- 2017-06-06 2017-06-05 2017-06-04 2017-06-03 2017-06-02

User · Answer

improved with weekday an joining a custom holiday table microsoft MSSQL 2012 for powerpivot date table https   gist github com josy1024 cb1487d66d9e0ccbd420bc4a23b6e90e  with  dates  as       select convert datetime   2016-01-01   as  date  --start     union all     select dateadd day  1   date       from  dates      where  date   lt   2018-01-01  --end   select  date    DATEPART  dw  date   as Wochentag    select holidayname from holidaytable  where holidaytable hdate    date    as Feiertag from  dates  where  date  between  2016-01-01  and  2016-31-12  option  maxrecursion 0

User · Answer

One more solution for mysql 8 0 1 and mariadb 10 2 2 using recursive common table expressions   with recursive dates as       select  2010-01-20  as date     union all     select date   interval 1 day from dates where date  lt   2010-01-24    select   from dates

User · Answer

Shorter than accepted answer  same idea    SELECT TRIM  2016-01-05    INTERVAL a   b DAY  date FROM  SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9   d   SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40  m WHERE  2016-01-05    INTERVAL a   b DAY   lt     2016-01-21

User · Answer

Can create a procedure also to create calendar table with timestmap different from day  If you want a table for each quarter   e g   2019-01-22 08 45 00 2019-01-22 09 00 00 2019-01-22 09 15 00 2019-01-22 09 30 00 2019-01-22 09 45 00 2019-01-22 10 00 00   you can use   CREATE DEFINER  root   localhost  PROCEDURE  generate calendar table    BEGIN  select unix timestamp  2014-01-01 00 00 00   into  startts  select unix timestamp  2025-01-01 00 00 00   into  endts   if    startts  lt   endts   then      DROP TEMPORARY TABLE IF EXISTS calendar table tmp       CREATE TEMPORARY TABLE calendar table tmp  ts int  dt datetime         WHILE    startts  lt   endts          DO          SET  startts    startts   900          INSERT calendar table tmp VALUES   startts  from unixtime  startts        END WHILE   END if   END     and then manipulate through  select ts  dt from calendar table tmp     that give you also ts   1548143100    2019-01-22 08 45 00   1548144000    2019-01-22 09 00 00   1548144900    2019-01-22 09 15 00   1548145800    2019-01-22 09 30 00   1548146700    2019-01-22 09 45 00   1548147600    2019-01-22 10 00 00    from here you can start to add other information such as   select ts  dt  weekday dt  as wd from calendar table tmp     or create a real table with create table statement

User · Answer

Alright   Try this  http   www devshed com c a MySQL Delving-Deeper-into-MySQL-50  http   dev mysql com doc refman 5 0 en loop-statement html http   www roseindia net sql mysql-example mysql-loop shtml    Use that to  say  generate a temp table  and then do a select   on the temp table  Or output the results one at a time  What you say you want to do can t be done with a SELECT statement  but it might be doable with things specific to MySQL  Then again  maybe you need cursors  http   dev mysql com doc refman 5 0 en cursors html

User · Answer

MSSQL Query  select datetable Date  from       select DATEADD day - a a    10   b a     100   c a   getdate    AS Date     from  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as a      cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as b      cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4      union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as c   datetable where datetable Date between  2014-01-20  and  2014-01-24   order by datetable Date DESC   Output  Date ----- 2014-01-23 12 35 25 250 2014-01-22 12 35 25 250 2014-01-21 12 35 25 250 2014-01-20 12 35 25 250

User · Answer

Using a recursive Common Table Expression  CTE   you can generate a list of dates  then select from it  Obviously you normally wouldn t want to create three million dates  so this just illustrates the possibilities  You could simply limit the date range inside the CTE and omit the where clause from the select statement using the CTE   with  dates  as       select convert datetime   1753-01-01   as  date  --start     union all     select dateadd day  1   date       from  dates      where  date   lt   9999-12-31  --end   select  date  from  dates  where  date  between  2013-01-01  and  2013-12-31  option  maxrecursion 0    On Microsoft SQL Server 2005  generating the CTE list of all possible dates took 1 08  Generating one hundred years took less than a second

User · Answer

For Oracle  my solution is   select trunc sysdate-dayincrement   DD      from dual   select level as dayincrement                  from dual connect by level  lt   30    Sysdate can be changed to specific date and level number can be changed to give more dates

User · Answer

It s a good idea with generating these dates on the fly  However  I do not feel myself comfortable to do this with quite large range so I ve ended up with the following solution    Created a table  DatesNumbers  that will hold numbers used for dates calculation    CREATE TABLE DatesNumbers       i MEDIUMINT NOT NULL      PRIMARY KEY  i    COMMENT  Used by Dates view       Populated the table using above techniques with numbers from -59999 to 40000  This range will give me dates from 59999 days   164 years  behind to 40000 days  109 years  ahead    INSERT INTO DatesNumbers SELECT      a i    10   b i     100   c i     1000   d i     10000   e i  - 59999 AS i FROM     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  AS a     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  AS b     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  AS c     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  AS d     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  AS e      Created a view  Dates     SELECT       i        CURRENT DATE     INTERVAL i DAY AS Date FROM     DatesNumbers   That s it        Easy to read queries     No on the fly numbers generations      Gives dates in the past and in the future and there is NO UNION in view for this as in this post       In the past only  or  in the future only  dates could be filtered using WHERE i  lt  0 or WHERE i  gt  0  PK   -   temporary  table  amp  view is used

User · Answer

The old school solution for doing this without a loop cursor is to create a NUMBERS table  which has a single Integer column with values starting at 1     CREATE TABLE   example   numbers       id  int 10  unsigned NOT NULL auto increment    PRIMARY KEY    id     ENGINE InnoDB DEFAULT CHARSET latin1    You need to populate the table with enough records to cover your needs   INSERT INTO NUMBERS  id  VALUES  NULL     Once you have the NUMBERS table  you can use   SELECT x start date   INTERVAL n id-1 DAY   FROM NUMBERS n   JOIN  SELECT STR TO DATE  2010-01-20     Y- m- d   AS start date            FROM DUAL  x  WHERE x start date   INTERVAL n id-1 DAY  lt    2010-01-24    The absolute low-tech solution would be   SELECT STR TO DATE  2010-01-20     Y- m- d    FROM DUAL UNION ALL SELECT STR TO DATE  2010-01-21     Y- m- d    FROM DUAL UNION ALL SELECT STR TO DATE  2010-01-22     Y- m- d    FROM DUAL UNION ALL SELECT STR TO DATE  2010-01-23     Y- m- d    FROM DUAL UNION ALL SELECT STR TO DATE  2010-01-24     Y- m- d    FROM DUAL   What would you use it for     To generate lists of dates or numbers in order to LEFT JOIN on to   You would to this in order to see where there are gaps in the data  because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist

User · Answer

You d like to get the a date range   In your example you d like to get the dates between  2010-01-20  and  2010-01-24   possible solution    select date add  2010-01-20   interval row day  from         SELECT  row     row   1 as row FROM       select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t       select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t2        select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t3        select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t4        SELECT  row  -1  r    sequence  where date add  2010-01-20   interval row day   lt    2010-01-24    Explanation  MySQL has a date add function so  select date add  2010-01-20   interval 1 day    will give you  2010-01-21   The datediff function would let you know often you d have to repeat this  select datediff  2010-01-24    2010-01-20     which returns   4   Getting a list of dates in a date range boils down to creating a sequence of integer numbers see generate an integer sequence in MySQL  The most upvoted answer here has taken a similar approach as https   stackoverflow com a 2652051 1497139 as a basis   SELECT  row     row   1 as row FROM   select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t   select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t2    select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t3    select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t4    SELECT  row  0  r limit 4   which will result in  row 1 0 2 0 3 0 4 0   The rows can now be used to create a list of dates from the given start date  To include the start date we start with row -1   select date add  2010-01-20   interval row day  from         SELECT  row     row   1 as row FROM       select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t       select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t2        select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t3        select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9  t4        SELECT  row  -1  r    sequence  where date add  2010-01-20   interval row day   lt    2010-01-24

User · Answer

This solution uses no loops  procedures  or temp tables  The subquery generates dates for the last 10 000 days  and could be extended to go as far back or forward as you wish   select a Date  from       select curdate   - INTERVAL  a a    10   b a     100   c a     1000   d a    DAY as Date     from  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as a     cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as b     cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as c     cross join  select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9  as d   a where a Date between  2010-01-20  and  2010-01-24     Output   Date ---------- 2010-01-24 2010-01-23 2010-01-22 2010-01-21 2010-01-20   Notes on Performance  Testing it out here  the performance is surprisingly good  the above query takes 0 0009 sec   If we extend the subquery to generate approx  100 000 numbers  and thus about 274 years worth of dates   it runs in 0 0458 sec   Incidentally  this is a very portable technique that works with most databases with minor adjustments   SQL Fiddle example returning 1 000 days

User · Answer

As stated  or at least alluded to  in many of the wonderful answers already given  this problem is easily solved once you have a set of numbers to work with   Note  The following is T-SQL but it s simply my particular implementation of general concepts already mentioned here and on the internet at large  It should be relatively simple to convert the code to your dialect of choice   How  Consider this query   SELECT DATEADD d  N   0001-01-22   FROM Numbers -- A table containing the numbers 0 through N WHERE N  lt   5    The above produces the date range 1 22 0001 - 1 27 0001 and is extremely trivial  There are 2 key pieces of information in the above query  the start date of 0001-01-22 and the offset of 5  If we combine these two pieces of information then we obviously have our end date  Thus  given two dates  generating a range can be broken down like so    Find the difference between two given dates  the offset   easy   -- Returns 125  SELECT ABS DATEDIFF d   2014-08-22    2014-12-25     Using ABS   here ensures that the date order is irrelevant  Generate a limited set of numbers  also easy   -- Returns the numbers 0-2 SELECT N   ROW NUMBER   OVER ORDER BY  SELECT NULL   - 1  FROM SELECT  A  AS S UNION ALL SELECT  A  UNION ALL SELECT  A    Notice we don t actually care what we re selecting FROM here  We just need a set to work with so that we count the number of rows in it  I personally use a TVF  some use a CTE  others use a numbers table instead  you get the idea  I advocate for using the most performant solution that you also understand    Combining these two methods will solve our problem   DECLARE  date1 DATE    9001-11-21   DECLARE  date2 DATE    9001-11-23    SELECT D   DATEADD d  N   date1  FROM       SELECT N   ROW NUMBER   OVER ORDER BY  SELECT NULL   - 1     FROM  SELECT  A  AS S UNION ALL SELECT  A  UNION ALL SELECT  A   S   Numbers WHERE N  lt   ABS DATEDIFF d   date1   date2      The above example is horrible code but demonstrates how everything comes together   More Fun  I need to do this kind of thing a lot so I encapsulated the logic into two TVFs  The first generates a range of numbers and the second uses this functionality to generate a range of dates  The math is to ensure that input order doesn t matter and because I wanted to use the full range of numbers available in GenerateRangeSmallInt   The following function takes  16ms of CPU time to return the maximum range of 65536 dates   CREATE FUNCTION dbo GenerateRangeDate           date1 DATE          date2 DATE         RETURNS TABLE WITH SCHEMABINDING    AS    RETURN       SELECT D   DATEADD d  N   32768  CASE WHEN  date1  lt    date2 THEN  date1 ELSE  date2 END      FROM dbo GenerateRangeSmallInt -32768  ABS DATEDIFF d   date1   date2   - 32768      GO  CREATE FUNCTION dbo GenerateRangeSmallInt        num1 SMALLINT   -32768      num2 SMALLINT   32767   RETURNS TABLE WITH SCHEMABINDING AS RETURN       WITH Numbers N  AS           SELECT N FROM VALUES              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 16              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 32              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 48              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 64              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 80              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 96              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 112              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 128              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 144              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 160              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 176              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 192              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 208              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 224              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 240              1    1    1    1    1    1    1    1    1    1    1    1    1    1    1    1  -- 256           V  N            SELECT TOP ABS CAST  num1 AS INT  - CAST  num2 AS INT     1             N   ROW NUMBER   OVER ORDER BY  SELECT NULL     CASE WHEN  num1  lt    num2 THEN  num1 ELSE  num2 END - 1     FROM Numbers A          Numbers B

[sql] generate days from date range

Examples related to sql

Examples related to mysql

Examples related to datetime

Examples related to between