MySQL - how to show all date from given range date

Question

I have records like this:

id | name | date
1  | aji  | 2014-06-01
2  | akon | 2014-06-02
3  | dewi | 2014-06-04
4  | dani | 2014-06-05

I want to display all dates from 2014-06-01 - 2014-06-06, even if they have no records associated:

id   | name | date
1    | aji  | 2014-06-01
2    | akon | 2014-06-02
NULL | NULL | 2014-06-03
3    | dewi | 2014-06-04
4    | dani | 2014-06-05
NULL | NULL | 2014-06-06

possible duplicate of [MySQL: query between two dates?](http://stackoverflow.com/questions/3822648/mysql-query-between-two-dates) — user986959, Jul 10 '14 at 08:35
@user986959, i need to display record eventhough record in that date is empty — user3824520, Jul 10 '14 at 08:43

gkrishy · Answer 1 · 2014-07-10T10:37:38.580

1

Possibly you can use this way.

SQL Server

Select IsNull(id, Null), IsNull(name,Null),date 
   where date Between  2014-06-01 and 2014-06-06.

If ID has set to int, then do like this

Select IsNull(id, 0), IsNull(name,Null),date 
   where date Between  2014-06-01 and 2014-06-06.

MY SQL

select id, name,date from tr_kehadiran 
    where date >= "2014-06-30" AND date <= "2014-07-06" AND id is Null AND name is Null

Please let me know the result.

edited Jul 10 '14 at 10:37

answered Jul 10 '14 at 08:51

gkrishy

756
1
8
33

Query: select IsNull(id, 0), IsNull(name,0),date from tr_kehadiran where date >= "2014-06-30" AND date <= "2014-07-06" Error Code: 1582 Incorrect parameter count in the call to native function 'IsNull' – user3824520 Jul 10 '14 at 08:59
it display records without 2014-06-03 and 2014-06-06 – user3824520 Jul 10 '14 at 09:30
Check it now. It's my mistake. Now I changed the IfNull syntax please have a look on my answer part. – gkrishy Jul 10 '14 at 09:32
Try it now. I have changed the query. Hope it may helps you. – gkrishy Jul 10 '14 at 10:39

score 1 · Answer 2 · answered Jul 10 '14 at 09:10

I'm not sure i understand what you're asking for. But

SELECT * FROM table WHERE date BETWEEN  '2014-06-01' AND '2014-06-06'

And

<?php
$start = new DateTime('2014-06-01');
$interval = new DateInterval('P1D');
$end = new DateTime('2014-06-06');

$period = new DatePeriod($start, $interval, $end);

foreach ($period as $date)
{
    if (isset($results['date'][$date]))
    {
        // Results display
    }
    else
    {
        // NULL display
    }
}

where $results is the result of your query

score 0 · Answer 3 · edited Aug 10 '17 at 14:40

Following are the steps to be followed -

If SQL Server

Using CTE, populate a range of date needed as per your requirement
Left join your table against the CTE on the date column and populate the dates missing in your result set from CTE

If MySQL -

Create a temporary table to populate the date range as per the requirement
Left join the temporary table on the date column and populate the dates missing in your result set from CTE

MySQL - how to show all date from given range date

3 Answers3