SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Building calendar table using MTVF

By Darko Martinovic,

SELECT [Calendar].[DetermineEaster](2016,0) ->Corpus Christ
SELECT [Calendar].[DetermineEaster](2016,1) ->Easter
SELECT dateAdd(day,-46,[Calendar].[DetermineEaster](2016,1)) --Powdery mildew
SELECT dateAdd(day,-7,[Calendar].[DetermineEaster](2016,1)) --Palm Sunday

SELECT
*
FROM CALENDAR.EASTERS(2017, 2018);
TheYear     TheEaster  TheCorpusChrist
----------- ---------- ---------------
2017        2017-04-16 2017-06-15
2018        2018-04-01 2018-05-31

SELECT
*
FROM CALENDAR.HOLIDAYS('20170101', '20171231');
TheDate    HolidayName
---------- ----------------------------------------------------------------------------------------------------
2017-01-01 New Year
2017-01-06 Epiphany
2017-04-16 Easter
2017-04-17 Easter Monday
2017-05-01 Labor Day
2017-06-15 Corpus Christi
2017-06-22 Anti-Fascist Struggle Day
2017-06-25 Statehood Day
2017-08-05 Victory and Homeland Thanksgiving Day and the Day of Croatian Defenders
2017-08-15 Assumption of Mary
2017-10-08 Independence Day
2017-11-01 All Saints'Day
2017-12-25 Christmas
2017-12-26 St. Stephen's Day

SELECT
CalendarDate
,DayOfTheWeek
,IsWeekEnd
,isHoliday
,isWorkedDay
,WorkedDayNo
FROM Calendar.Calendar('20170101', '20170131');
CalendarDate DayOfTheWeek IsWeekEnd isHoliday isWorkedDay WorkedDayNo
------------ ------------ --------- --------- ----------- -----------
2017-01-01   1            1         1         0           1
2017-01-02   2            0         0         1           1
2017-01-03   3            0         0         1           2
2017-01-04   4            0         0         1           3
2017-01-05   5            0         0         1           4
2017-01-06   6            0         1         0           2
2017-01-07   0            1         0         0           3
2017-01-08   1            1         0         0           4
2017-01-09   2            0         0         1           5
2017-01-10   3            0         0         1           6
2017-01-11   4            0         0         1           7
2017-01-12   5            0         0         1           8
2017-01-13   6            0         0         1           9
2017-01-14   0            1         0         0           5
2017-01-15   1            1         0         0           6
2017-01-16   2            0         0         1           10
2017-01-17   3            0         0         1           11
2017-01-18   4            0         0         1           12
2017-01-19   5            0         0         1           13
2017-01-20   6            0         0         1           14
2017-01-21   0            1         0         0           7
2017-01-22   1            1         0         0           8
2017-01-23   2            0         0         1           15
2017-01-24   3            0         0         1           16
2017-01-25   4            0         0         1           17
2017-01-26   5            0         0         1           18
2017-01-27   6            0         0         1           19
2017-01-28   0            1         0         0           9
2017-01-29   1            1         0         0           10
2017-01-30   2            0         0         1           20
2017-01-31   3            0         0         1           21

Total article views: 960 | Views in the last 30 days: 390
 
Related Articles
BLOG

An Easter SQL

Since Easter is nearly upon us, I got to thinking once again about how challenging it is to calculat...

ARTICLE

Happy Easter

Happy Easter wishes from SQLServerCentral.

SCRIPT

Determine Easter Sunday

Determine what day easter sunday will be based upon the year you pass the function

ARTICLE

Happy Easter 2012

Happy Easter 2012 from SQLServerCentral.

SCRIPT

Date Calendar

Code to create a handy date calendar cross-reference table with a ton of pre-populated, slice-and-di...

Tags
t-sql    
 
Contribute