TSQL Date?

Hello All,

Is there some code or some existing data somewhere that i can use to create/dump into a new date table in SQLServer that has all the regular dates along with a column called "holiday" that says if that date is a holiday or not?

Thank you
RayneAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Haris DjulicConnect With a Mentor Commented:
Here is the code i used to setup my calendar table for period of 2000 -2030 ..

CREATE TABLE dbo.Calendar  
(  
    dt SMALLDATETIME NOT NULL 
        PRIMARY KEY CLUSTERED,  
     
    isWeekday BIT, 
    isHoliday BIT,  
    Y SMALLINT,  
    FY SMALLINT,  
    Q TINYINT,  
    M TINYINT,  
    D TINYINT,  
    DW TINYINT, 
    monthname VARCHAR(9), 
    dayname VARCHAR(9), 
    W TINYINT 
) 
GO

SET NOCOUNT ON 
DECLARE @dt SMALLDATETIME 
SET @dt = '20000101' 
WHILE @dt < '20300101' 
BEGIN 
    INSERT dbo.Calendar(dt) SELECT @dt 
    SET @dt = @dt + 1 
END


UPDATE dbo.Calendar SET 
 
    isWeekday = CASE  
        WHEN DATEPART(DW, dt) IN (1,7)  
        THEN 0 
        ELSE 1 END, 
 
    isHoliday = 0, 
 
    Y = YEAR(dt),  
 
    FY = YEAR(dt), 
 
    /* 
    -- if our fiscal year 
    -- starts on May 1st: 
 
    FY = CASE  
        WHEN MONTH(dt) < 5 
        THEN YEAR(dt)-1  
        ELSE YEAR(dt) END, 
    */ 
 
    Q = CASE 
        WHEN MONTH(dt) <= 3 THEN 1 
        WHEN MONTH(dt) <= 6 THEN 2 
        WHEN MONTH(dt) <= 9 THEN 3 
        ELSE 4 END,  
 
    M = MONTH(dt),  
 
    D = DAY(dt),  
 
    DW = DATEPART(DW, dt),  
 
    monthname = DATENAME(MONTH, dt),  
 
    dayname = DATENAME(DW, dt),  
 
    W = DATEPART(WK, dt)


UPDATE dbo.Calendar
   SET IsHoliday = 0;

-- New Year's day
UPDATE Calendar 
   SET IsHoliday = 1
 WHERE M = 1 
   AND D = 1;

-- New Year's day observed
--  if NYD is a Saturday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 12
   AND c1.D = 31
   AND c1.DW = 6
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 1
                  AND c2.D = 1
                  AND c2.dt > c1.dt
                  AND DW = 7);

-- if NYD is a Sunday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 1
   AND c1.D = 2
   AND c1.DW = 2
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 1
                  AND c2.D = 1
                  AND c2.dt < c1.dt
                  AND c2.DW = 1);


-- Martin Luther King Day
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 1
   AND c1.DW = 2
   AND (SELECT COUNT(*)
          FROM Calendar AS c2
         WHERE c2.M = 1
           AND c2.DW = 2
           AND c2.Y = c1.Y
           AND c2.dt < c1.dt) = 2;

-- Washington's Birthday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 2
   AND c1.DW = 2
   AND (SELECT COUNT(*)
          FROM Calendar AS c2
         WHERE c2.M = 2
           AND c2.DW = 2
           AND c2.Y = c1.Y
           AND c2.dt < c1.dt) = 2;

-- Memorial Day
UPDATE Calendar 
   SET IsHoliday = 1
  FROM Calendar AS c1 
 WHERE M = 5 
   AND DW = 2 
   AND NOT EXISTS (SELECT 1 
                     FROM Calendar AS c2 
                    WHERE M = 5 AND DW = 2 
                      AND c2.Y = c1.Y 
                      AND c2.dt > c1.dt);

-- Independence Day
UPDATE Calendar
   SET IsHoliday = 1
 WHERE M = 7
   AND D = 4;

-- Independence Day, observed
--  if Independence Day is a Saturday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 7
   AND c1.D = 3
   AND c1.DW = 6
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 7
                  AND c2.D = 4
                  AND c2.DW = 7
                  AND c2.dt > c1.dt);

--  if Independence Day is a Sunday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 7
   AND c1.D = 5
   AND c1.DW = 2
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 7
                  AND c2.D = 4
                  AND c2.DW = 1
                  AND c2.dt < c1.dt);

-- Labor Day
UPDATE Calendar 
    SET IsHoliday = 1
   FROM Calendar AS c1 
  WHERE M = 9 
    AND DW = 2 
    AND NOT EXISTS (SELECT 1 
                      FROM Calendar AS c2 
                     WHERE M = 9 AND DW = 2 
                       AND c2.Y = c1.Y 
                       AND c2.dt < c1.dt);

-- Columbus Day
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 10
   AND c1.DW = 2
   AND (SELECT COUNT(*)
          FROM Calendar AS c2
         WHERE c2.M = 10
           AND c2.DW = 2
           AND c2.Y = c1.Y
           AND c1.dt > c2.dt) = 1

-- Veterans' Day
UPDATE Calendar 
   SET IsHoliday = 1
 WHERE M = 11 
   AND D = 11 

-- Veterans' Day, observed
--  when Veterans' Day is a Saturday
UPDATE Calendar 
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 11 
   AND c1.D = 10
   AND c1.DW = 6
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 11
                  AND c2.D = 11
                  AND c2.DW = 7
                  AND c2.dt > c1.dt);

--  when Veterans' Day is a Sunday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 11
   AND c1.D = 12
   AND c1.DW = 2
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 11
                  AND c2.D = 11
                  AND c2.DW = 1
                  AND c2.dt < c1.dt);

-- Thanksgiving Day
UPDATE Calendar 
   SET IsHoliday = 1
  FROM Calendar AS c1 
 WHERE M = 11 
   AND DW = 5 
   AND (SELECT COUNT(*) 
          FROM Calendar AS c2 
         WHERE M = 11 
           AND DW = 5 
           AND c2.Y = c1.Y 
           AND c2.dt < c1.dt) = 3;

-- Christmas Day
UPDATE Calendar
   SET IsHoliday = 1
 WHERE M = 12
   AND D = 25;

-- Christmas Day, observed
--  when Christmas Day is a Saturday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 12
   AND c1.D = 24
   AND c1.DW = 6
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 12
                  AND c2.D = 25
                  AND c2.DW = 7
                  AND c2.dt > c1.dt);

--  when Christmas Day is a Sunday
UPDATE Calendar
   SET IsHoliday = 1
  FROM Calendar AS c1
 WHERE c1.M = 12
   AND c1.D = 26
   AND c1.DW = 2
   AND EXISTS (SELECT 1
                 FROM Calendar AS c2
                WHERE c2.M = 12
                  AND c2.D = 25
                  AND c2.DW = 1
                  AND c2.dt < c1.dt);

Open in new window


code can be found and tested here: http://sqlfiddle.com/#!6/635fe/2
0
 
RayneAuthor Commented:
I meant for federal US Holidays
0
 
HuaMinChenSystem managerCommented:
You can use one insert statements like

insert into holi_tab values (convert(datetime,'20140101',112))
...
insert into holi_tab values (convert(datetime,'20141225',112))

Open in new window

as the holidays are different for different country/area
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
HuaMinChenSystem managerCommented:
Please see the holidays below

http://www.timeanddate.com/calendar/?country=1

for USA.

And you can directly detect if the day is Sat or Sun, to know if it is holiday.
0
 
RayneAuthor Commented:
I need the federal US holiday from 2013 to 2020....so the fastest way to get them....just viewing will not help
0
 
HuaMinChenSystem managerCommented:
You can just prepare the relevant insert lines for all the years you need. Thanks
0
 
RayneAuthor Commented:
Basically what you are suggesting is copy paste all that picture into excel and keep doing it for all the years....there gotta be a better way - a list of dates in a data table or something than this visual really..
0
 
HuaMinChenSystem managerCommented:
The point is you should keep such holiday table up to date, for all years. Thanks
0
 
HuaMinChenConnect With a Mentor System managerCommented:
By yourself.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
As the above experts state, do a Google search/go to the links they provide to get US Federal Holidays, and manually insert them into a table.  SQL Server has no function to get US Federal, New York City, Manchester United, or the Rick Springfield Fan Club of East Orange, New Jersey holdays, as they are wildly different and can change.

If you want to expand your question to 'How to I create a calendar table with dates so I can pick off custom holidays', then this article is a demo with code on how to pull that off. , complete with logic on dealing with holidays such as 'The third Thursday in November'.
0
All Courses

From novice to tech pro — start learning today.