Get a list of dates between two dates

Question

Using standard mysql functions is there a way to write a query that will return a list of days between two dates   eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values    2009-01-01   2009-01-02   2009-01-03  2009-01-04   2009-01-05  2009-01-06  2009-01-07  2009-01-08   2009-01-09  2009-01-10  2009-01-11  2009-01-12  2009-01-13   Edit  It appears I have not been clear  I want to GENERATE this list  I have values stored in the database  by datetime  but want them to be aggregated on a left outer join to a list of dates as above  I am expecting null from the right side of some of this join for some days and will handle this

User · Answer

You can use MySQL s user variables like this   SET  num   -1  SELECT DATE ADD   2009-01-01   interval  num     num 1 day  AS date sequence   your table   FROM your table WHERE your table other column IS NOT NULL HAVING DATE ADD  2009-01-01   interval  num day   lt    2009-01-13     num is -1 because you add to it the first time you use it  Also  you can t use  HAVING date sequence  because that makes the user variable increment twice for each row

User · Answer

You can use this SELECT CAST cal date list AS DATE  day year FROM     SELECT SUBDATE  2019-01-01   INTERVAL 1 YEAR    INTERVAL xc DAY AS date list   FROM           SELECT  xi   xi 1 as xc from          SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc1           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc2           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc3           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc4           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc5           SELECT  xi  -1  xc0       xxc1   cal WHERE cal date list BETWEEN  2019-01-01  AND  2019-12-31  ORDER BY cal date list DESC

User · Answer

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this   SELECT   FROM       SELECT DATEADD d  number - 1   2009-01-01   AS dt     FROM Numbers     WHERE number BETWEEN 1 AND DATEDIFF d   2009-01-01    2009-01-13     1   AS DateRange LEFT JOIN YourStuff     ON DateRange dt   YourStuff DateColumn   I ve seen variations with table-valued functions  etc   You can also keep a permanent list of dates   We have that in our data warehouse as well as a list of times of day

User · Answer

I ve been fighting with this for quite a while   Since this is the first hit on Google when I searched for the solution  let me post where I ve gotten so far   SET  d     2011-09-01   SELECT  d AS d  cast   d    DATE ADD   d   INTERVAL 1 DAY   AS DATE   AS new d   FROM  yourTable    WHERE  d  lt    2012-05-01     Replace  yourTable  with a table from your database  The trick is that the number of rows in the table you select must be    the number of dates you want to be returned   I tried using the table placeholder DUAL  but it would only return one single row

User · Answer

For MSSQL you can use this  It is VERY quick  You can wrap this up in a table valued function or stored proc and parse in the start and end dates as variables  DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate    2011-01-01  SET  endDate    2011-01-31    WITH dates Date  AS        SELECT  startdate as Date     UNION ALL     SELECT DATEADD d 1  Date       FROM dates      WHERE DATE  lt   enddate    SELECT Date FROM dates OPTION  MAXRECURSION 0  GO  Edit 2021 01  Dr  V   I liked this solution and made it work for mySQL V8  Here is the code  wrapping it into a procedure  DELIMITER     CREATE PROCEDURE dates between  IN from date DATETIME                                 IN to date DATETIME  BEGIN     WITH RECURSIVE dates Date  AS               SELECT from date as Date         UNION ALL         SELECT DATE ADD Date  INTERVAL 1 day  FROM dates WHERE Date  lt  to date           SELECT DATE Date  FROM dates  END    DELIMITER

User · Answer

I would use this stored procedure to generate the intervals you need into the temp table named time intervals  then JOIN and aggregate your data table with the temp time intervals table   The procedure can generate intervals of all the different types you see specified in it     call make intervals  2009-01-01 00 00 00   2009-01-10 00 00 00  1  DAY     select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 23 59 59  2009-01-02 00 00 00 2009-01-02 23 59 59  2009-01-03 00 00 00 2009-01-03 23 59 59  2009-01-04 00 00 00 2009-01-04 23 59 59  2009-01-05 00 00 00 2009-01-05 23 59 59  2009-01-06 00 00 00 2009-01-06 23 59 59  2009-01-07 00 00 00 2009-01-07 23 59 59  2009-01-08 00 00 00 2009-01-08 23 59 59  2009-01-09 00 00 00 2009-01-09 23 59 59    call make intervals  2009-01-01 00 00 00   2009-01-01 02 00 00  10  MINUTE      select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 00 09 59  2009-01-01 00 10 00 2009-01-01 00 19 59  2009-01-01 00 20 00 2009-01-01 00 29 59  2009-01-01 00 30 00 2009-01-01 00 39 59  2009-01-01 00 40 00 2009-01-01 00 49 59  2009-01-01 00 50 00 2009-01-01 00 59 59  2009-01-01 01 00 00 2009-01-01 01 09 59  2009-01-01 01 10 00 2009-01-01 01 19 59  2009-01-01 01 20 00 2009-01-01 01 29 59  2009-01-01 01 30 00 2009-01-01 01 39 59  2009-01-01 01 40 00 2009-01-01 01 49 59  2009-01-01 01 50 00 2009-01-01 01 59 59    I specified an interval start and interval end so you can aggregate the  data timestamps with a  between interval start and interval end  type of JOIN    Code for the proc    -- drop procedure make intervals   CREATE PROCEDURE make intervals startdate timestamp  enddate timestamp  intval integer  unitval varchar 10   BEGIN --                                                                           -- Procedure  make intervals   --    Author  Ron Savage --      Date  02 03 2009 -- -- Description  -- This procedure creates a temporary table named time intervals with the -- interval start and interval end fields specifed from the startdate and -- enddate arguments  at intervals of intval  unitval  size  --                                                                              declare thisDate timestamp     declare nextDate timestamp     set thisDate   startdate      --                                                                              -- Drop   create the temp table    --                                                                              drop temporary table if exists time intervals     create temporary table if not exists time intervals               interval start timestamp        interval end timestamp              --                                                                              -- Loop through the startdate adding each intval interval until enddate    --                                                                              repeat       select          case unitval             when  MICROSECOND  then timestampadd MICROSECOND  intval  thisDate              when  SECOND       then timestampadd SECOND  intval  thisDate              when  MINUTE       then timestampadd MINUTE  intval  thisDate              when  HOUR         then timestampadd HOUR  intval  thisDate              when  DAY          then timestampadd DAY  intval  thisDate              when  WEEK         then timestampadd WEEK  intval  thisDate              when  MONTH        then timestampadd MONTH  intval  thisDate              when  QUARTER      then timestampadd QUARTER  intval  thisDate              when  YEAR         then timestampadd YEAR  intval  thisDate           end into nextDate         insert into time intervals select thisDate  timestampadd MICROSECOND  -1  nextDate         set thisDate   nextDate     until thisDate  gt   enddate    end repeat    END    Similar example data scenario at the bottom of this post  where I built a similar function for SQL Server

User · Answer

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data  Since there were no entries for those dates  the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date   We have a job that runs every month to ensure that the table is populated 5 years out into the future  The table is created thus   create table all dates       dt date primary key      No doubt there are magical tricky ways to do this with different DBMS  but we always opt for the simplest solution  The storage requirements for the table are minimal and it makes the queries so much simpler and portable  This sort of solution is almost always better from a performance point-of-view since it doesn t require per-row calculations on the data   The other option  and we ve used this before  is to ensure there s an entry in the table for every date  We swept the table periodically and added zero entries for dates and or times that didn t exist  This may not be an option in your case  it depends on the data stored   If you really think it s a hassle to keep the all dates table populated  a stored procedure is the way to go which will return a dataset containing those dates  This will almost certainly be slower since you have to calculate the range every time it s called rather than just pulling pre-calculated data from a table   But  to be honest  you could populate the table out for 1000 years without any serious data storage problems - 365 000 16-byte  for example  dates plus an index duplicating the date plus 20  overhead for safety  I d roughly estimate at about 14M  365 000   16   2   1 2   14 016 000 bytes    a minuscule table in the scheme of things

User · Answer

This solution is working with MySQL 5 0 Create a table - mytable  The schema does not material  What matters is the number of rows in it  So  you can keep just one column of type INT with 10 rows  values - 1 to 10   SQL   set  tempDate date  2011-07-01   - interval 1 day  select date  tempDate     date  tempDate    interval 1 day   as theDate from mytable x mytable y group by theDate having theDate  lt    2011-07-31     Limitation  The maximum number of dates returned by above query will be   rows in mytable   rows in mytable    10 10   100   You can increase this range by changing form part in sql  from mytable x mytable y  mytable z So  the range be 10 10 10  1000 and so on

User · Answer

I would use this stored procedure to generate the intervals you need into the temp table named time intervals  then JOIN and aggregate your data table with the temp time intervals table   The procedure can generate intervals of all the different types you see specified in it     call make intervals  2009-01-01 00 00 00   2009-01-10 00 00 00  1  DAY     select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 23 59 59  2009-01-02 00 00 00 2009-01-02 23 59 59  2009-01-03 00 00 00 2009-01-03 23 59 59  2009-01-04 00 00 00 2009-01-04 23 59 59  2009-01-05 00 00 00 2009-01-05 23 59 59  2009-01-06 00 00 00 2009-01-06 23 59 59  2009-01-07 00 00 00 2009-01-07 23 59 59  2009-01-08 00 00 00 2009-01-08 23 59 59  2009-01-09 00 00 00 2009-01-09 23 59 59    call make intervals  2009-01-01 00 00 00   2009-01-01 02 00 00  10  MINUTE      select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 00 09 59  2009-01-01 00 10 00 2009-01-01 00 19 59  2009-01-01 00 20 00 2009-01-01 00 29 59  2009-01-01 00 30 00 2009-01-01 00 39 59  2009-01-01 00 40 00 2009-01-01 00 49 59  2009-01-01 00 50 00 2009-01-01 00 59 59  2009-01-01 01 00 00 2009-01-01 01 09 59  2009-01-01 01 10 00 2009-01-01 01 19 59  2009-01-01 01 20 00 2009-01-01 01 29 59  2009-01-01 01 30 00 2009-01-01 01 39 59  2009-01-01 01 40 00 2009-01-01 01 49 59  2009-01-01 01 50 00 2009-01-01 01 59 59    I specified an interval start and interval end so you can aggregate the  data timestamps with a  between interval start and interval end  type of JOIN    Code for the proc    -- drop procedure make intervals   CREATE PROCEDURE make intervals startdate timestamp  enddate timestamp  intval integer  unitval varchar 10   BEGIN --                                                                           -- Procedure  make intervals   --    Author  Ron Savage --      Date  02 03 2009 -- -- Description  -- This procedure creates a temporary table named time intervals with the -- interval start and interval end fields specifed from the startdate and -- enddate arguments  at intervals of intval  unitval  size  --                                                                              declare thisDate timestamp     declare nextDate timestamp     set thisDate   startdate      --                                                                              -- Drop   create the temp table    --                                                                              drop temporary table if exists time intervals     create temporary table if not exists time intervals               interval start timestamp        interval end timestamp              --                                                                              -- Loop through the startdate adding each intval interval until enddate    --                                                                              repeat       select          case unitval             when  MICROSECOND  then timestampadd MICROSECOND  intval  thisDate              when  SECOND       then timestampadd SECOND  intval  thisDate              when  MINUTE       then timestampadd MINUTE  intval  thisDate              when  HOUR         then timestampadd HOUR  intval  thisDate              when  DAY          then timestampadd DAY  intval  thisDate              when  WEEK         then timestampadd WEEK  intval  thisDate              when  MONTH        then timestampadd MONTH  intval  thisDate              when  QUARTER      then timestampadd QUARTER  intval  thisDate              when  YEAR         then timestampadd YEAR  intval  thisDate           end into nextDate         insert into time intervals select thisDate  timestampadd MICROSECOND  -1  nextDate         set thisDate   nextDate     until thisDate  gt   enddate    end repeat    END    Similar example data scenario at the bottom of this post  where I built a similar function for SQL Server

User · Answer

Well how to find dates between two given date in SQL server is explain on http   ektaraval blogspot com 2010 09 writing-recursive-query-to-find-out-all html

User · Answer

We used this in our HRMS System you will find it useful  SELECT CAST DAYNAME daydate  as CHAR  as dayname daydate     FROM      select CAST  date add  20110101   interval H i 100   T i 10   U i day   as DATE  as daydate       from erp integers as H     cross       join erp integers as T     cross       join erp integers as U      where date add  20110101   interval H i 100   T i 10   U i day    lt    20110228      order         by daydate ASC          Days

User · Answer

I ve been fighting with this for quite a while   Since this is the first hit on Google when I searched for the solution  let me post where I ve gotten so far   SET  d     2011-09-01   SELECT  d AS d  cast   d    DATE ADD   d   INTERVAL 1 DAY   AS DATE   AS new d   FROM  yourTable    WHERE  d  lt    2012-05-01     Replace  yourTable  with a table from your database  The trick is that the number of rows in the table you select must be    the number of dates you want to be returned   I tried using the table placeholder DUAL  but it would only return one single row

User · Answer

CREATE FUNCTION  dbo    DATES         startDate DATETIME       endDate DATETIME   RETURNS   DATES TABLE      DATE1 DATETIME   AS BEGIN     WHILE  startDate  lt    endDate     BEGIN          INSERT INTO  DATES  DATE1              SELECT  startDate        SELECT  startDate   DATEADD d 1  startDate       END RETURN END

User · Answer

Borrowing an idea from this answer  you can set up a table with 0 through 9 and use that to generate your list of dates   CREATE TABLE num  i int   INSERT INTO num  i  VALUES  0    1    2    3    4    5    6    7    8    9    select adddate  2009-01-01   numlist id  as  date  from  SELECT n1 i   n10 i 10   n100 i 100 AS id    FROM num n1 cross join num as n10 cross join num as n100  as numlist where adddate  2009-01-01   numlist id   lt    2009-01-13     This will allow you to generate a list of up to 1000 dates   If you need to go larger  you can add another cross join to the inner query

User · Answer

For Access  or any SQL language    Create one table that has 2 fields  we ll call this table tempRunDates   --Fields fromDate and toDate  --Then insert only 1 record  that has the start date and the end date  Create another table  Time Day Ref --Import a list of dates  make list in excel is easy  into this table   --The field name in my case is Greg Dt  for Gregorian Date --I made my list from jan 1 2009 through jan 1 2020  Run the query   SELECT Time Day Ref GREG DT FROM tempRunDates  Time Day Ref WHERE Time Day Ref greg dt gt  tempRunDates fromDate And greg dt lt  tempRunDates toDate     Easy

User · Answer

select   from table name where col Date between  2011 02 25  AND DATEADD s -1 DATEADD d 1  2011 02 27      Here  first add a day to the current endDate  it will be 2011-02-28 00 00 00  then you subtract one second to make the end date 2011-02-27 23 59 59  By doing this  you can get all the dates between the given intervals   output  2011 02 25 2011 02 26 2011 02 27

User · Answer

I would use this stored procedure to generate the intervals you need into the temp table named time intervals  then JOIN and aggregate your data table with the temp time intervals table   The procedure can generate intervals of all the different types you see specified in it     call make intervals  2009-01-01 00 00 00   2009-01-10 00 00 00  1  DAY     select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 23 59 59  2009-01-02 00 00 00 2009-01-02 23 59 59  2009-01-03 00 00 00 2009-01-03 23 59 59  2009-01-04 00 00 00 2009-01-04 23 59 59  2009-01-05 00 00 00 2009-01-05 23 59 59  2009-01-06 00 00 00 2009-01-06 23 59 59  2009-01-07 00 00 00 2009-01-07 23 59 59  2009-01-08 00 00 00 2009-01-08 23 59 59  2009-01-09 00 00 00 2009-01-09 23 59 59    call make intervals  2009-01-01 00 00 00   2009-01-01 02 00 00  10  MINUTE      select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 00 09 59  2009-01-01 00 10 00 2009-01-01 00 19 59  2009-01-01 00 20 00 2009-01-01 00 29 59  2009-01-01 00 30 00 2009-01-01 00 39 59  2009-01-01 00 40 00 2009-01-01 00 49 59  2009-01-01 00 50 00 2009-01-01 00 59 59  2009-01-01 01 00 00 2009-01-01 01 09 59  2009-01-01 01 10 00 2009-01-01 01 19 59  2009-01-01 01 20 00 2009-01-01 01 29 59  2009-01-01 01 30 00 2009-01-01 01 39 59  2009-01-01 01 40 00 2009-01-01 01 49 59  2009-01-01 01 50 00 2009-01-01 01 59 59    I specified an interval start and interval end so you can aggregate the  data timestamps with a  between interval start and interval end  type of JOIN    Code for the proc    -- drop procedure make intervals   CREATE PROCEDURE make intervals startdate timestamp  enddate timestamp  intval integer  unitval varchar 10   BEGIN --                                                                           -- Procedure  make intervals   --    Author  Ron Savage --      Date  02 03 2009 -- -- Description  -- This procedure creates a temporary table named time intervals with the -- interval start and interval end fields specifed from the startdate and -- enddate arguments  at intervals of intval  unitval  size  --                                                                              declare thisDate timestamp     declare nextDate timestamp     set thisDate   startdate      --                                                                              -- Drop   create the temp table    --                                                                              drop temporary table if exists time intervals     create temporary table if not exists time intervals               interval start timestamp        interval end timestamp              --                                                                              -- Loop through the startdate adding each intval interval until enddate    --                                                                              repeat       select          case unitval             when  MICROSECOND  then timestampadd MICROSECOND  intval  thisDate              when  SECOND       then timestampadd SECOND  intval  thisDate              when  MINUTE       then timestampadd MINUTE  intval  thisDate              when  HOUR         then timestampadd HOUR  intval  thisDate              when  DAY          then timestampadd DAY  intval  thisDate              when  WEEK         then timestampadd WEEK  intval  thisDate              when  MONTH        then timestampadd MONTH  intval  thisDate              when  QUARTER      then timestampadd QUARTER  intval  thisDate              when  YEAR         then timestampadd YEAR  intval  thisDate           end into nextDate         insert into time intervals select thisDate  timestampadd MICROSECOND  -1  nextDate         set thisDate   nextDate     until thisDate  gt   enddate    end repeat    END    Similar example data scenario at the bottom of this post  where I built a similar function for SQL Server

User · Answer

You can use MySQL s user variables like this   SET  num   -1  SELECT DATE ADD   2009-01-01   interval  num     num 1 day  AS date sequence   your table   FROM your table WHERE your table other column IS NOT NULL HAVING DATE ADD  2009-01-01   interval  num day   lt    2009-01-13     num is -1 because you add to it the first time you use it  Also  you can t use  HAVING date sequence  because that makes the user variable increment twice for each row

User · Answer

I would use something similar to this   DECLARE  DATEFROM AS DATETIME DECLARE  DATETO AS DATETIME DECLARE  HOLDER TABLE DATE DATETIME   SET  DATEFROM    2010-08-10  SET  DATETO    2010-09-11   INSERT INTO      HOLDER          DATE  VALUES       DATEFROM   WHILE  DATEFROM  lt   DATETO BEGIN      SELECT  DATEFROM   DATEADD D  1   DATEFROM      INSERT      INTO          HOLDER              DATE      VALUES           DATEFROM  END  SELECT      DATE FROM      HOLDER   Then the  HOLDER Variable table holds all the dates incremented by day between those two dates  ready to join at your hearts content

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

User · Answer

DELIMITER      CREATE PROCEDURE popula calendario controle      BEGIN       DECLARE a INT Default 0        DECLARE first day of year DATE        set first day of year   CONCAT DATE FORMAT curdate     Y    -01-01          one by one  LOOP          IF dayofweek adddate first day of year a    lt  gt  1 THEN             INSERT INTO calendario controle VALUES null 150 adddate first day of year a  adddate first day of year a  1            END IF           SET a a 1           IF a 365 THEN             LEAVE one by one           END IF        END LOOP one by one  END      this procedure will insert all dates from the beginning of the year till now  just substitue the days of the  start  and  end   and you are ready to go

User · Answer

select   from table name where col Date between  2011 02 25  AND DATEADD s -1 DATEADD d 1  2011 02 27      Here  first add a day to the current endDate  it will be 2011-02-28 00 00 00  then you subtract one second to make the end date 2011-02-27 23 59 59  By doing this  you can get all the dates between the given intervals   output  2011 02 25 2011 02 26 2011 02 27

User · Answer

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this   SELECT   FROM       SELECT DATEADD d  number - 1   2009-01-01   AS dt     FROM Numbers     WHERE number BETWEEN 1 AND DATEDIFF d   2009-01-01    2009-01-13     1   AS DateRange LEFT JOIN YourStuff     ON DateRange dt   YourStuff DateColumn   I ve seen variations with table-valued functions  etc   You can also keep a permanent list of dates   We have that in our data warehouse as well as a list of times of day

User · Answer

Create a stored procedure which takes two parameters a begin and a end  Create a temporary table within it called t  declare a variable d  assign a begin to d  and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d  Finally SELECT   FROM t

User · Answer

I needed a list with all months between 2 dates for statistics  The 2 dates are the start and enddate from a subscription   So the list shows all months and the amount of subscriptions per month   MYSQL  CREATE PROCEDURE  get amount subscription per month    BEGIN    -- Select the ultimate start and enddate from subscribers    select  startdate    min DATE FORMAT a startdate    Y- m-01                 enddate    max DATE FORMAT a enddate    Y- m-01      interval 1 MONTH    from subscription a      -- Tmp table with all months  dates   you can always format them with DATE FORMAT      DROP TABLE IF EXISTS tmp months     create temporary table tmp months         year month date        PRIMARY KEY  year month             set  tempDate  startdate    - interval 1 MONTH      -- Insert every month in tmp table    WHILE  tempDate  lt    enddate DO      insert into tmp months  year month  values   tempDate        set  tempDate    date  tempDate    interval 1 MONTH      END WHILE      -- All months    select year month from tmp months      -- If you want the amount of subscription per month else leave it out    select mnd year month  sum subscription amount  as subscription amount    from tmp months mnd    LEFT JOIN subscription ON mnd year month  gt   DATE FORMAT subscription startdate    Y- m-01   and mnd year month  lt   DATE FORMAT subscription enddate    Y- m-01      GROUP BY mnd year month    END

User · Answer

CREATE FUNCTION  dbo    DATES         startDate DATETIME       endDate DATETIME   RETURNS   DATES TABLE      DATE1 DATETIME   AS BEGIN     WHILE  startDate  lt    endDate     BEGIN          INSERT INTO  DATES  DATE1              SELECT  startDate        SELECT  startDate   DATEADD d 1  startDate       END RETURN END

User · Answer

I am using Server version  5 7 11-log MySQL Community Server  GPL   Now we will solve this in a simple way   I have created a table named  datetable   mysql gt  describe datetable   --------- --------- ------ ----- --------- -------    Field     Type      Null   Key   Default   Extra    --------- --------- ------ ----- --------- -------    colid     int 11    NO     PRI   NULL                coldate   date      YES          NULL               --------- --------- ------ ----- --------- -------  2 rows in set  0 00 sec    now  wee will see the inserted records within   mysql gt  select   from datetable   ------- ------------    colid   coldate       ------- ------------      101   2015-01-01       102   2015-05-01       103   2016-01-01    ------- ------------  3 rows in set  0 00 sec    and here our query to fetch records within two dates rather than those dates   mysql gt  select   from datetable where coldate  gt   2015-01-01  and coldate  lt   2016-01-01    ------- ------------    colid   coldate       ------- ------------      102   2015-05-01    ------- ------------  1 row in set  0 00 sec    hope this would help many ones

User · Answer

You can use MySQL s user variables like this   SET  num   -1  SELECT DATE ADD   2009-01-01   interval  num     num 1 day  AS date sequence   your table   FROM your table WHERE your table other column IS NOT NULL HAVING DATE ADD  2009-01-01   interval  num day   lt    2009-01-13     num is -1 because you add to it the first time you use it  Also  you can t use  HAVING date sequence  because that makes the user variable increment twice for each row

User · Answer

Well how to find dates between two given date in SQL server is explain on http   ektaraval blogspot com 2010 09 writing-recursive-query-to-find-out-all html

User · Answer

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data  Since there were no entries for those dates  the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date   We have a job that runs every month to ensure that the table is populated 5 years out into the future  The table is created thus   create table all dates       dt date primary key      No doubt there are magical tricky ways to do this with different DBMS  but we always opt for the simplest solution  The storage requirements for the table are minimal and it makes the queries so much simpler and portable  This sort of solution is almost always better from a performance point-of-view since it doesn t require per-row calculations on the data   The other option  and we ve used this before  is to ensure there s an entry in the table for every date  We swept the table periodically and added zero entries for dates and or times that didn t exist  This may not be an option in your case  it depends on the data stored   If you really think it s a hassle to keep the all dates table populated  a stored procedure is the way to go which will return a dataset containing those dates  This will almost certainly be slower since you have to calculate the range every time it s called rather than just pulling pre-calculated data from a table   But  to be honest  you could populate the table out for 1000 years without any serious data storage problems - 365 000 16-byte  for example  dates plus an index duplicating the date plus 20  overhead for safety  I d roughly estimate at about 14M  365 000   16   2   1 2   14 016 000 bytes    a minuscule table in the scheme of things

User · Answer

For Access  or any SQL language    Create one table that has 2 fields  we ll call this table tempRunDates   --Fields fromDate and toDate  --Then insert only 1 record  that has the start date and the end date  Create another table  Time Day Ref --Import a list of dates  make list in excel is easy  into this table   --The field name in my case is Greg Dt  for Gregorian Date --I made my list from jan 1 2009 through jan 1 2020  Run the query   SELECT Time Day Ref GREG DT FROM tempRunDates  Time Day Ref WHERE Time Day Ref greg dt gt  tempRunDates fromDate And greg dt lt  tempRunDates toDate     Easy

User · Answer

Create a stored procedure which takes two parameters a begin and a end  Create a temporary table within it called t  declare a variable d  assign a begin to d  and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d  Finally SELECT   FROM t

User · Answer

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data  Since there were no entries for those dates  the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date   We have a job that runs every month to ensure that the table is populated 5 years out into the future  The table is created thus   create table all dates       dt date primary key      No doubt there are magical tricky ways to do this with different DBMS  but we always opt for the simplest solution  The storage requirements for the table are minimal and it makes the queries so much simpler and portable  This sort of solution is almost always better from a performance point-of-view since it doesn t require per-row calculations on the data   The other option  and we ve used this before  is to ensure there s an entry in the table for every date  We swept the table periodically and added zero entries for dates and or times that didn t exist  This may not be an option in your case  it depends on the data stored   If you really think it s a hassle to keep the all dates table populated  a stored procedure is the way to go which will return a dataset containing those dates  This will almost certainly be slower since you have to calculate the range every time it s called rather than just pulling pre-calculated data from a table   But  to be honest  you could populate the table out for 1000 years without any serious data storage problems - 365 000 16-byte  for example  dates plus an index duplicating the date plus 20  overhead for safety  I d roughly estimate at about 14M  365 000   16   2   1 2   14 016 000 bytes    a minuscule table in the scheme of things

User · Answer

Borrowing an idea from this answer  you can set up a table with 0 through 9 and use that to generate your list of dates   CREATE TABLE num  i int   INSERT INTO num  i  VALUES  0    1    2    3    4    5    6    7    8    9    select adddate  2009-01-01   numlist id  as  date  from  SELECT n1 i   n10 i 10   n100 i 100 AS id    FROM num n1 cross join num as n10 cross join num as n100  as numlist where adddate  2009-01-01   numlist id   lt    2009-01-13     This will allow you to generate a list of up to 1000 dates   If you need to go larger  you can add another cross join to the inner query

User · Answer

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this   SELECT   FROM       SELECT DATEADD d  number - 1   2009-01-01   AS dt     FROM Numbers     WHERE number BETWEEN 1 AND DATEDIFF d   2009-01-01    2009-01-13     1   AS DateRange LEFT JOIN YourStuff     ON DateRange dt   YourStuff DateColumn   I ve seen variations with table-valued functions  etc   You can also keep a permanent list of dates   We have that in our data warehouse as well as a list of times of day

User · Answer

Borrowing an idea from this answer  you can set up a table with 0 through 9 and use that to generate your list of dates   CREATE TABLE num  i int   INSERT INTO num  i  VALUES  0    1    2    3    4    5    6    7    8    9    select adddate  2009-01-01   numlist id  as  date  from  SELECT n1 i   n10 i 10   n100 i 100 AS id    FROM num n1 cross join num as n10 cross join num as n100  as numlist where adddate  2009-01-01   numlist id   lt    2009-01-13     This will allow you to generate a list of up to 1000 dates   If you need to go larger  you can add another cross join to the inner query

User · Answer

I am using Server version  5 7 11-log MySQL Community Server  GPL   Now we will solve this in a simple way   I have created a table named  datetable   mysql gt  describe datetable   --------- --------- ------ ----- --------- -------    Field     Type      Null   Key   Default   Extra    --------- --------- ------ ----- --------- -------    colid     int 11    NO     PRI   NULL                coldate   date      YES          NULL               --------- --------- ------ ----- --------- -------  2 rows in set  0 00 sec    now  wee will see the inserted records within   mysql gt  select   from datetable   ------- ------------    colid   coldate       ------- ------------      101   2015-01-01       102   2015-05-01       103   2016-01-01    ------- ------------  3 rows in set  0 00 sec    and here our query to fetch records within two dates rather than those dates   mysql gt  select   from datetable where coldate  gt   2015-01-01  and coldate  lt   2016-01-01    ------- ------------    colid   coldate       ------- ------------      102   2015-05-01    ------- ------------  1 row in set  0 00 sec    hope this would help many ones

User · Answer

Create a stored procedure which takes two parameters a begin and a end  Create a temporary table within it called t  declare a variable d  assign a begin to d  and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d  Finally SELECT   FROM t

User · Answer

You can use MySQL s user variables like this   SET  num   -1  SELECT DATE ADD   2009-01-01   interval  num     num 1 day  AS date sequence   your table   FROM your table WHERE your table other column IS NOT NULL HAVING DATE ADD  2009-01-01   interval  num day   lt    2009-01-13     num is -1 because you add to it the first time you use it  Also  you can t use  HAVING date sequence  because that makes the user variable increment twice for each row

User · Answer

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data  Since there were no entries for those dates  the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date   We have a job that runs every month to ensure that the table is populated 5 years out into the future  The table is created thus   create table all dates       dt date primary key      No doubt there are magical tricky ways to do this with different DBMS  but we always opt for the simplest solution  The storage requirements for the table are minimal and it makes the queries so much simpler and portable  This sort of solution is almost always better from a performance point-of-view since it doesn t require per-row calculations on the data   The other option  and we ve used this before  is to ensure there s an entry in the table for every date  We swept the table periodically and added zero entries for dates and or times that didn t exist  This may not be an option in your case  it depends on the data stored   If you really think it s a hassle to keep the all dates table populated  a stored procedure is the way to go which will return a dataset containing those dates  This will almost certainly be slower since you have to calculate the range every time it s called rather than just pulling pre-calculated data from a table   But  to be honest  you could populate the table out for 1000 years without any serious data storage problems - 365 000 16-byte  for example  dates plus an index duplicating the date plus 20  overhead for safety  I d roughly estimate at about 14M  365 000   16   2   1 2   14 016 000 bytes    a minuscule table in the scheme of things

User · Answer

Borrowing an idea from this answer  you can set up a table with 0 through 9 and use that to generate your list of dates   CREATE TABLE num  i int   INSERT INTO num  i  VALUES  0    1    2    3    4    5    6    7    8    9    select adddate  2009-01-01   numlist id  as  date  from  SELECT n1 i   n10 i 10   n100 i 100 AS id    FROM num n1 cross join num as n10 cross join num as n100  as numlist where adddate  2009-01-01   numlist id   lt    2009-01-13     This will allow you to generate a list of up to 1000 dates   If you need to go larger  you can add another cross join to the inner query

User · Answer

DELIMITER      CREATE PROCEDURE popula calendario controle      BEGIN       DECLARE a INT Default 0        DECLARE first day of year DATE        set first day of year   CONCAT DATE FORMAT curdate     Y    -01-01          one by one  LOOP          IF dayofweek adddate first day of year a    lt  gt  1 THEN             INSERT INTO calendario controle VALUES null 150 adddate first day of year a  adddate first day of year a  1            END IF           SET a a 1           IF a 365 THEN             LEAVE one by one           END IF        END LOOP one by one  END      this procedure will insert all dates from the beginning of the year till now  just substitue the days of the  start  and  end   and you are ready to go

User · Answer

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this   SELECT   FROM       SELECT DATEADD d  number - 1   2009-01-01   AS dt     FROM Numbers     WHERE number BETWEEN 1 AND DATEDIFF d   2009-01-01    2009-01-13     1   AS DateRange LEFT JOIN YourStuff     ON DateRange dt   YourStuff DateColumn   I ve seen variations with table-valued functions  etc   You can also keep a permanent list of dates   We have that in our data warehouse as well as a list of times of day

User · Answer

I would use something similar to this   DECLARE  DATEFROM AS DATETIME DECLARE  DATETO AS DATETIME DECLARE  HOLDER TABLE DATE DATETIME   SET  DATEFROM    2010-08-10  SET  DATETO    2010-09-11   INSERT INTO      HOLDER          DATE  VALUES       DATEFROM   WHILE  DATEFROM  lt   DATETO BEGIN      SELECT  DATEFROM   DATEADD D  1   DATEFROM      INSERT      INTO          HOLDER              DATE      VALUES           DATEFROM  END  SELECT      DATE FROM      HOLDER   Then the  HOLDER Variable table holds all the dates incremented by day between those two dates  ready to join at your hearts content

User · Answer

I would use this stored procedure to generate the intervals you need into the temp table named time intervals  then JOIN and aggregate your data table with the temp time intervals table   The procedure can generate intervals of all the different types you see specified in it     call make intervals  2009-01-01 00 00 00   2009-01-10 00 00 00  1  DAY     select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 23 59 59  2009-01-02 00 00 00 2009-01-02 23 59 59  2009-01-03 00 00 00 2009-01-03 23 59 59  2009-01-04 00 00 00 2009-01-04 23 59 59  2009-01-05 00 00 00 2009-01-05 23 59 59  2009-01-06 00 00 00 2009-01-06 23 59 59  2009-01-07 00 00 00 2009-01-07 23 59 59  2009-01-08 00 00 00 2009-01-08 23 59 59  2009-01-09 00 00 00 2009-01-09 23 59 59    call make intervals  2009-01-01 00 00 00   2009-01-01 02 00 00  10  MINUTE      select   from time intervals     interval start      interval end         ------------------- -------------------  2009-01-01 00 00 00 2009-01-01 00 09 59  2009-01-01 00 10 00 2009-01-01 00 19 59  2009-01-01 00 20 00 2009-01-01 00 29 59  2009-01-01 00 30 00 2009-01-01 00 39 59  2009-01-01 00 40 00 2009-01-01 00 49 59  2009-01-01 00 50 00 2009-01-01 00 59 59  2009-01-01 01 00 00 2009-01-01 01 09 59  2009-01-01 01 10 00 2009-01-01 01 19 59  2009-01-01 01 20 00 2009-01-01 01 29 59  2009-01-01 01 30 00 2009-01-01 01 39 59  2009-01-01 01 40 00 2009-01-01 01 49 59  2009-01-01 01 50 00 2009-01-01 01 59 59    I specified an interval start and interval end so you can aggregate the  data timestamps with a  between interval start and interval end  type of JOIN    Code for the proc    -- drop procedure make intervals   CREATE PROCEDURE make intervals startdate timestamp  enddate timestamp  intval integer  unitval varchar 10   BEGIN --                                                                           -- Procedure  make intervals   --    Author  Ron Savage --      Date  02 03 2009 -- -- Description  -- This procedure creates a temporary table named time intervals with the -- interval start and interval end fields specifed from the startdate and -- enddate arguments  at intervals of intval  unitval  size  --                                                                              declare thisDate timestamp     declare nextDate timestamp     set thisDate   startdate      --                                                                              -- Drop   create the temp table    --                                                                              drop temporary table if exists time intervals     create temporary table if not exists time intervals               interval start timestamp        interval end timestamp              --                                                                              -- Loop through the startdate adding each intval interval until enddate    --                                                                              repeat       select          case unitval             when  MICROSECOND  then timestampadd MICROSECOND  intval  thisDate              when  SECOND       then timestampadd SECOND  intval  thisDate              when  MINUTE       then timestampadd MINUTE  intval  thisDate              when  HOUR         then timestampadd HOUR  intval  thisDate              when  DAY          then timestampadd DAY  intval  thisDate              when  WEEK         then timestampadd WEEK  intval  thisDate              when  MONTH        then timestampadd MONTH  intval  thisDate              when  QUARTER      then timestampadd QUARTER  intval  thisDate              when  YEAR         then timestampadd YEAR  intval  thisDate           end into nextDate         insert into time intervals select thisDate  timestampadd MICROSECOND  -1  nextDate         set thisDate   nextDate     until thisDate  gt   enddate    end repeat    END    Similar example data scenario at the bottom of this post  where I built a similar function for SQL Server

User · Answer

For MSSQL you can use this  It is VERY quick  You can wrap this up in a table valued function or stored proc and parse in the start and end dates as variables  DECLARE  startDate DATETIME DECLARE  endDate DATETIME  SET  startDate    2011-01-01  SET  endDate    2011-01-31    WITH dates Date  AS        SELECT  startdate as Date     UNION ALL     SELECT DATEADD d 1  Date       FROM dates      WHERE DATE  lt   enddate    SELECT Date FROM dates OPTION  MAXRECURSION 0  GO  Edit 2021 01  Dr  V   I liked this solution and made it work for mySQL V8  Here is the code  wrapping it into a procedure  DELIMITER     CREATE PROCEDURE dates between  IN from date DATETIME                                 IN to date DATETIME  BEGIN     WITH RECURSIVE dates Date  AS               SELECT from date as Date         UNION ALL         SELECT DATE ADD Date  INTERVAL 1 day  FROM dates WHERE Date  lt  to date           SELECT DATE Date  FROM dates  END    DELIMITER

User · Answer

We used this in our HRMS System you will find it useful  SELECT CAST DAYNAME daydate  as CHAR  as dayname daydate     FROM      select CAST  date add  20110101   interval H i 100   T i 10   U i day   as DATE  as daydate       from erp integers as H     cross       join erp integers as T     cross       join erp integers as U      where date add  20110101   interval H i 100   T i 10   U i day    lt    20110228      order         by daydate ASC          Days

User · Answer

I needed a list with all months between 2 dates for statistics  The 2 dates are the start and enddate from a subscription   So the list shows all months and the amount of subscriptions per month   MYSQL  CREATE PROCEDURE  get amount subscription per month    BEGIN    -- Select the ultimate start and enddate from subscribers    select  startdate    min DATE FORMAT a startdate    Y- m-01                 enddate    max DATE FORMAT a enddate    Y- m-01      interval 1 MONTH    from subscription a      -- Tmp table with all months  dates   you can always format them with DATE FORMAT      DROP TABLE IF EXISTS tmp months     create temporary table tmp months         year month date        PRIMARY KEY  year month             set  tempDate  startdate    - interval 1 MONTH      -- Insert every month in tmp table    WHILE  tempDate  lt    enddate DO      insert into tmp months  year month  values   tempDate        set  tempDate    date  tempDate    interval 1 MONTH      END WHILE      -- All months    select year month from tmp months      -- If you want the amount of subscription per month else leave it out    select mnd year month  sum subscription amount  as subscription amount    from tmp months mnd    LEFT JOIN subscription ON mnd year month  gt   DATE FORMAT subscription startdate    Y- m-01   and mnd year month  lt   DATE FORMAT subscription enddate    Y- m-01      GROUP BY mnd year month    END

User · Answer

This solution is working with MySQL 5 0 Create a table - mytable  The schema does not material  What matters is the number of rows in it  So  you can keep just one column of type INT with 10 rows  values - 1 to 10   SQL   set  tempDate date  2011-07-01   - interval 1 day  select date  tempDate     date  tempDate    interval 1 day   as theDate from mytable x mytable y group by theDate having theDate  lt    2011-07-31     Limitation  The maximum number of dates returned by above query will be   rows in mytable   rows in mytable    10 10   100   You can increase this range by changing form part in sql  from mytable x mytable y  mytable z So  the range be 10 10 10  1000 and so on

User · Answer

You can use this SELECT CAST cal date list AS DATE  day year FROM     SELECT SUBDATE  2019-01-01   INTERVAL 1 YEAR    INTERVAL xc DAY AS date list   FROM           SELECT  xi   xi 1 as xc from          SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc1           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc2           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc3           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc4           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4  xc5           SELECT  xi  -1  xc0       xxc1   cal WHERE cal date list BETWEEN  2019-01-01  AND  2019-12-31  ORDER BY cal date list DESC

User · Answer

Create a stored procedure which takes two parameters a begin and a end  Create a temporary table within it called t  declare a variable d  assign a begin to d  and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d  Finally SELECT   FROM t

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

[mysql] Get a list of dates between two dates

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to gaps-and-islands