Search code examples
mysqloutsystems

Select all the dates in a year

I have a list of date and I want them to be enabled in the calendarwidget of my application. The calendar widgets allows me to give only the list of dates that needs to disabled.

enter image description here

I can convert the above dates in to the format 2015-3-6,2015-3-8,2015-3-7,2015-5-4,2015-5-7,2015-5-12,2015-6-16,2015-7-2,2015-10-19

But I need to get the rest of the dates expect the above dates in the calendar in the above format. I am not sure how do I get. Any idea to how I can achieve this is really appreciated

Solution

  • TABLE

    CREATE TABLE Available
    (`IsAvailable` datetime);

INSERT INTO Available
    (`IsAvailable`)
VALUES
    ('2015-03-06 00:00:00'),        ('2015-03-08 00:00:00'),
    ('2015-03-07 00:00:00'),        ('2015-05-04 00:00:00'),
    ('2015-05-07 00:00:00'),        ('2015-05-12 00:00:00'),
    ('2015-06-16 00:00:00'),        ('2015-07-02 00:00:00'),
    ('2015-10-19 00:00:00');

    SQL Fiddle Demo

    select y2015.selected_date,
       IF(Available.`IsAvailable` IS NULL, FALSE, TRUE) as Available
from 
     (select adddate('2015-01-01', t2.i*100 + t1.i*10 + t0.i) selected_date 
      from
         (select 0 i 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 i 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 i 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
     ) y2015
LEFT JOIN Available
       ON y2015.selected_date = Available.`IsAvailable`   
where y2015.selected_date < '2016-01-01'

    OUTPUT

    | selected_date | Available |
|---------------|-----------|
|    2015-03-06 |         1 |   \
|    2015-03-08 |         1 |    \
|    2015-03-07 |         1 |    |
|    2015-05-04 |         1 |    \
|    2015-05-07 |         1 |    /  Those are in Avaliable table
|    2015-05-12 |         1 |    |
|    2015-06-16 |         1 |    |
|    2015-07-02 |         1 |    /
|    2015-10-19 |         1 |   /
|    2015-01-01 |         0 |
|    2015-01-02 |         0 |
|    2015-01-03 |         0 |
|    2015-01-04 |         0 |
|    2015-01-05 |         0 |
|    2015-01-06 |         0 |
|    2015-01-07 |         0 |
....
|    2015-12-31 |         0 |

   Record Count: 365;