Select all the dates in a year

Question

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.

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

Possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — Juan Carlos Oropeza, Mar 03 '16 at 15:27
@JuanCarlosOropeza I did check the post you mentioned. But I dont want to use the stored procedure here. Is there a way without using stored procedure. — trx, Mar 03 '16 at 15:28
The issue is I have the enabled date in last year and even in this year. So I need to have all the dates of last year and this year so I can generate he list of disabled dates having the enabled dates. I am not sure how do I do that pretty much new to mysql and outsystems — trx, Mar 03 '16 at 15:31
OK, what about this one ? http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query — Juan Carlos Oropeza, Mar 03 '16 at 15:32
I know nothing about OutSystems. However, it seems that it plays nicely with javascript, so you could generate a list of dates in javascript. — Strawberry, Mar 03 '16 at 15:43
Can you please help me with how to get disabled dates because all that I have is the set of dates that are enabled one. So I need to have all the dates between 01-01-2015 to 01-01-2016 , to generate the list of disabled dates. — trx, Mar 03 '16 at 16:00
@Strawberry Can we achieve this only with using the queries please. I am new to outsystems aswell — trx, Mar 03 '16 at 16:18

score 1 · Accepted Answer · answered Mar 03 '16 at 16:51

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;

I am lttle confused. Do I need to create a table called y2015 with selected_date and IsAvailable field. As I have the dates you inserted in the Available already on SnapshotDate so I am not creating the table Available. Can you please explain lil in detail how the above works please — trx, Mar 07 '16 at 18:15
y2015 isnt a table, is just a subquery (lets call it A). You can make it a table if you want. Also you can change parameters to have the date range you want. I Create `Available` (lets call it B) so I can test my query, you can use your table. Now you only need `(SELECT A.* FROM A LEFT JOIN B ON A.ID B.ID)` So what part you dont unerstand?? — Juan Carlos Oropeza, Mar 07 '16 at 20:26
As I have the dates in SnapShotDate table.And I dont want to disturb them. I am creating a table called AllDate with two fields selected_date and Available. Where I am going to have all the dates in 2015 and 2016, with the Available being 1 for all the dates that are in SnapshotDate table. So if I filter Available is 0 in AllDate Table it should me the remaining dates in 2015 and 2016 right. — trx, Mar 07 '16 at 20:52

Select all the dates in a year

1 Answers1