Czech holidays was valid 2022-09-03. Slovak holidays was valid 2022-11-04.
České svátky byly validní 9.3.2022 Slovenské svátky byly validní 11.4.2022
create function dbo.easter(@year integer)
returns datetime
as
begin
declare @K int, @M int, @S int, @A int, @D int, @R int, @OG int, @SZ int, @OE int, @OS int;
declare @easterdate datetime;
set @K = @year / 100;
set @M = 15 + (3*@K + 3) / 4 - (8*@K + 13) / 25;
set @S = 2 - (3*@K + 3) / 4;
set @A = @year % 19;
set @D = (19*@A + @M) % 30;
set @R = (@D + @A / 11) / 29;
set @OG = 21 + @D - @R ;
set @SZ = 7 - (@year + @year / 4 + @S) % 7;
set @OE = 7 - (@OG - @SZ) % 7;
set @OS = @OG + @OE;
set @easterdate = dateadd(day, @OS - 1, cast( cast(@year as nvarchar(4) )+'-03-01' as datetime ));
return @easterdate;
end
GO
create table Calendar (
id int primary key, -- year*10000+month*100+day - for example, 20220309
[date] datetime not null,
[year] smallint not null,
[month] tinyint not null, -- 1 to 12
[day] tinyint not null, -- 1 to 31
[quarter] tinyint not null, -- 1 to 4
[week] tinyint not null, -- 1 to 52/53
[weekend] bit not null,
[holiday_cs] bit not null,
[holiday_sk] bit not null,
constraint uq_year_mmonth_day unique([year], [month], [day]),
constraint uq_day unique([date])
);
set datefirst 1; -- monday is first day of week (Europe)
declare @date datetime;
set @date = cast('2000-01-01 00:00:00.000' as datetime);
declare @counter int;
set @counter = 1;
while @counter <= 36889 -- to 31.12.2100
begin
insert into Calendar
select year(@date)*10000+month(@date)*100+day(@date),
@date,
year(@date),
month(@date),
day(@date),
datepart(QUARTER, @date),
datepart(week, @date),
case when (((DATEPART(DW, @date) - 1 ) + @@DATEFIRST ) % 7) IN (0,6) then 1 else 0 end,
case month(@date)
when 1 then
case day(@date) when 1 then 1 else 0 -- nový rok
end
when 5 then
case day(@date)
when 1 then 1 -- Svátek práce
when 8 then 1 -- Den osvobození od fašismu
else 0
end
when 7 then
case day(@date)
when 5 then 1 -- Den slovanských věrozvěstů Cyrila a Metoděje
when 6 then 1 -- Den upálení mistra Jana Husa
else 0
end
when 9 then
case day(@date)
when 28 then 1 -- Den české státnosti
else 0
end
when 10 then
case day(@date)
when 28 then 1 -- Den vzniku samostatného československého státu
else 0
end
when 11 then
case day(@date)
when 17 then 1 -- Den boje za svobodu a demokracii
else 0
end
when 12 then
case day(@date)
when 24 then 1 -- Štědrý den
when 25 then 1 -- První vánoční svátek
when 26 then 1 -- Druhý vánoční svátek
else 0
end
else 0
end as [holiday_cs],
case month(@date)
when 1 then
case day(@date)
when 1 then 1 -- Den vzniku Slovenské republiky
when 6 then 1 -- Zjevení Páně (tři králové a vánoční svátek pravoslavných křesťanů)
else 0
end
when 5 then
case day(@date)
when 1 then 1 -- Svátek práce
when 8 then 1 -- Den vítězství nad fašismem
else 0
end
when 7 then
case day(@date)
when 5 then 1 -- Svátek sv. Cyrila a Metoděje a Den zahraničních Slováků
else 0
end
when 8 then
case day(@date)
when 29 then 1 -- Výročí Slovenského národního povstání
else 0
end
when 9 then
case day(@date)
when 1 then 1 -- Den ústavy SR
when 15 then 1 -- Panna Marie Sedmibolestná
else 0
end
when 11 then
case day(@date)
when 1 then 1 -- Svátek všech svatých
when 17 then 1 -- Den boje za svobodu a demokracii
else 0
end
when 12 then
case day(@date)
when 24 then 1 -- Štědrý den
when 25 then 1 -- První vánoční svátek
when 26 then 1 -- Druhý vánoční svátek
else 0
end
else 0
end as [holiday_sk]
set @counter = @counter + 1;
set @date = dateadd(DD, 1, @date);
end
declare @y smallint, @ymax smallint, @easter datetime;
select @y = min([year]), @ymax = max([year]) from Calendar;
while @y <= @ymax
begin
set @easter = dbo.easter(@y);
update Calendar set holiday_cs = 1, holiday_sk = 1
where [date] in (
dateadd(dd, -2, @easter), -- Velký pátek
dateadd(dd, 1, @easter) -- Velikonoční pondělí
);
set @y = @y + 1;
endGenerate once for lifetime and just use.
When you need to find workdays:
select calendar.date
from calendar
left join tasks on tasks.date = calendar.date and calendar.holiday_cs = 0 and calendar.weekend = 0
where tasks.date between '2022-3-1' and '2022-3-31'holidays but also weekend:
select [date] as [oh frak]
from Calendar
where holiday_cs = 1 and weekend = 1 and [year] = 2022;select [date] as [oh frak]
from Calendar
where holiday_sk = 1 and weekend = 1 and [year] = 2022;Etc...
Author of easter(year) function is jwaiher (https://github.com/jweiher/mysql-easter).
Rest is BSD licence (as in 'you can do whatever you want commercially or not, but you cant sue me')
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.