Calendar table in SQL

Question

Calendar table in SQL

pedroccamara

SSC-Addicted

Points: 431
More actions
July 15, 2022 at 9:55 am

#4063729

Hi guys
Could you please help me to build a calendar in SQL, with the normal holidays from Portugal. Is this possible?
Or....is there any chance of getting the holidays from Portugal for a specific year (2019-2022). I looked everywhere but i must be looking the wrong places....
Thank you all very much for helping me
Pedro

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

There are lots of sites showing the holidays (here, for example). Are you looking for something special?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Hello Phil

This is amazing!!Exactly what I needed

It's amazing how you guys always come up with such an easy answer.

Thank you soooooo much Phil!!

Thom A SSC Guru Points: 99130 More actions · Answer 3

pedroccamara wrote:

Or....is there any chance of getting the holidays from Portugal for a specific year (2019-2022).

Most internet calendars have details of public holidays now a days, so I would expect there to be a wealth of sites that list them. A search gave me a lots of sites and the first 2 (1, 2) seemed promising; are they missing critical information you need?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

pedroccamara SSC-Addicted Points: 431 More actions · Answer 4

Hi Tom,

Thank you very much for your answer. Phil already answered this one.

Also, now i know what was my "problem" not finding those calendars: i did the search in portuguese

Thanks a lot Tom

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Here's another listing that not only lists the fixed holidays that always occur on the same day of every year but also how to calculate the "movable" holidays.

https://en.wikipedia.org/wiki/Public_holidays_in_Portugal

Here's one of the easiest functions I've found to calculate Easter day with. BEFORE YOU USE IT... you should convert it to an Inline Table Valued Function (iTVF)

https://weblogs.sqlteam.com/peterl/2010/09/08/fast-easter-day-function/

Ya just gotta love the people that break down the math for the rest of us.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pedroccamara SSC-Addicted Points: 431 More actions · Answer 6

Hello Jeff

In fact it's impossible not to love those guys.

Thanks a lot for your message

Best regards

Pedro

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Shifting gears a bit, I remembered that I had refactored Peter's good code and found it in my archives. I redacted that to have a nice flower box and included the original date of Peter's code and my original redaction.

So, here's an "Easter Day" function already converted to an iTVF complete with a usage example.

 CREATE OR ALTER FUNCTION dbo.GetEasterDate
/****************************************************************************************
 Purpose:
 Given a 4 digit year from 1900 thru 9999, return the date of Easter Sunday.
-----------------------------------------------------------------------------------------
 Usage Example;
 SELECT edt.EasterDate
   FROM dbo.SomeTable st
  CROSS APPLY dbo.GetEasterDate(DATEPART(yy,st.SomeDateTimeColumn)) edt
;
-----------------------------------------------------------------------------------------
 Reference:
 Based on the work of Peter (Peso) Larsson, which can be found at the following URL:
 https://weblogs.sqlteam.com/peterl/2010/09/08/fast-easter-day-function/
-----------------------------------------------------------------------------------------
 Revision History:
 Rev 00 - 08 Sep 2010 - Peter Larsson
                      - Initial release and unit test.
 Rev 01 - 16 Feb 2013 - Jeff Moden
                      - "Modenize" the code to avoid string conversions
****************************************************************************************/
        (@Year SMALLINT)
RETURNS TABLE AS
 RETURN
 SELECT EasterDate = DATEADD(dd,DATEDIFF(dd,0,DATEFROMPARTS(@Year,c.MM,c.DD))/7*7,6)
   FROM (VALUES
         ( 0,04,15)
        ,( 1,04,04)
        ,( 2,03,24)
        ,( 3,04,12)
        ,( 4,04,01)
        ,( 5,04,19)
        ,( 6,04,09)
        ,( 7,03,29)
        ,( 8,04,17)
        ,( 9,04,06)
        ,(10,03,26)
        ,(11,04,14)
        ,(12,04,03)
        ,(13,03,23)
        ,(14,04,11)
        ,(15,03,31)
        ,(16,04,18)
        ,(17,04,08)
        ,(18,03,28)
        ) c (Cycle,MM,DD) --MM and DD are the yearless "Base Date" of each "Cycle"
  WHERE @Year BETWEEN 1900 AND 9999
    AND c.Cycle = @Year%19 --Easter dates are on a 19 year cycle since 1900
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pedroccamara SSC-Addicted Points: 431 More actions · Answer 8

Very good Jeff. Such a valuable information!!

Thank you so much

Have a great weekend!

Pedro

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

You bet. Thank you for the feedback, Pedro.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)