Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this
SELECT
user.*,
company.*.
DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);
You could take further and refactor the query as follows:
SELECT
U.*,
C.*.
DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;
From here, I modified the query slightly to get
select adddate('2015-02-01', numlist.id) as `my_date`,
weekday(adddate('2015-02-01', numlist.id)) as day_no,
dayname(adddate('2015-02-01', numlist.id)) as `day_name`
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('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');
which gives
+------------+--------+----------+
| my_date | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 | 0 | Monday |
| 2015-02-03 | 1 | Tuesday |
| 2015-02-09 | 0 | Monday |
| 2015-02-10 | 1 | Tuesday |
| 2015-02-16 | 0 | Monday |
| 2015-02-17 | 1 | Tuesday |
| 2015-02-23 | 0 | Monday |
| 2015-02-24 | 1 | Tuesday |
+------------+--------+----------+
8 rows in set (0.01 sec)
or from here
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');
result
+---------------+
| selected_date |
+---------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
+---------------+
8 rows in set (0.23 sec)
CREATE TABLE mydate( blah date);
INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
-- weekday(adddate('2015-02-01', numlist.id)) as day_no,
-- dayname(adddate('2015-02-01', numlist.id)) as `day_name`
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('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');
(need a table num)
mysql> select * from mydate;
+------------+
| blah |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |
OR
insert into mydate (blah)
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');
No table necessary. Same result as above.
Best Answer
If I'm understanding correct, you need only the days from the year of your second date. Based in the answer from dbdemon, you could use a CASE:
That way, if the dates are from the same year, the difference will be the same, if there from different year it'll count only starting from first day of the year.