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).
This question is related to
mysql
sql
date
gaps-and-islands
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 <= '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.
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) <= '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.
CREATE FUNCTION [dbo].[_DATES]
(
@startDate DATETIME,
@endDate DATETIME
)
RETURNS
@DATES TABLE(
DATE1 DATETIME
)
AS
BEGIN
WHILE @startDate <= @endDate
BEGIN
INSERT INTO @DATES (DATE1)
SELECT @startDate
SELECT @startDate = DATEADD(d,1,@startDate)
END
RETURN
END
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.
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>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
Easy!
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)) <> 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!
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) <= '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.
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) <= '2019-04-30'
)
select * FROM t;
The above returns a table of dates between '2019-01-01' and '2019-04-30'.
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) <= '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.
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 ) <= '20110228'
order
by daydate ASC
)Days
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.
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.
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;
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) <= '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.
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 <= @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 >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
GROUP BY mnd.year_month;
END
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 <= '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.
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
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.
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) <= '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.
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 <= '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.
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.
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) <= '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.
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)) <> 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!
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 <= '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.
CREATE FUNCTION [dbo].[_DATES]
(
@startDate DATETIME,
@endDate DATETIME
)
RETURNS
@DATES TABLE(
DATE1 DATETIME
)
AS
BEGIN
WHILE @startDate <= @endDate
BEGIN
INSERT INTO @DATES (DATE1)
SELECT @startDate
SELECT @startDate = DATEADD(d,1,@startDate)
END
RETURN
END
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 ) <= '20110228'
order
by daydate ASC
)Days
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) <= '2019-04-30'
)
select * FROM t;
The above returns a table of dates between '2019-01-01' and '2019-04-30'.
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 < @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.
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
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.
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> 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> 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> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate |
+-------+------------+
| 102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)
hope this would help many ones.
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.
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.
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) <= '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.
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 < @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 < to_date
)
SELECT DATE(Date) FROM dates;
END//
DELIMITER ;
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 < @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.
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.
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
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;
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.
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.
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> 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> 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> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate |
+-------+------------+
| 102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)
hope this would help many ones.
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.
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
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 <= @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 >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
GROUP BY mnd.year_month;
END
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) <= '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.
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 < @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 < to_date
)
SELECT DATE(Date) FROM dates;
END//
DELIMITER ;
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>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
Easy!
Source: Stackoverflow.com