Month: January 2011

Seems that the bug CORE-3302 is triggered when there are millions of rows and only in firebird 2.5 and 3.0 (Bug is already fixed in svn)
What you can learn from the bug is the usage of list,distinct,case,extract weekday in the same query

First i have created the query to extract the day in string format from current date
select
list(case extract(weekday from CURRENT_DATE) when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when 3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' when 6 then 'Sat' end)
from RDB$DATABASE
In my case the result was Fri.
Then you can create a table with different dates:

CREATE table tasks
(
DATE_OF_TASK DATE
);

Populate the table

INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-07');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-07');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-07');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-06');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-06');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-06');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-08');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-08');
INSERT INTO TASKS (DATE_OF_TASK) VALUES ('2011-01-08');

Then start to run the query

select
list(distinct case extract(weekday from date_of_task) when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tue' when 3 then 'Wed' when 4 then 'Thu' when 5 then 'Fri' when 6 then 'Sat' end)
from tasks

Result should be:

Fri,Sat,Thu