Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

TSQL Date?

Posted on 2014-10-08
10
211 Views
Last Modified: 2014-10-11
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
0
Comment
Question by:Rayne
10 Comments
 

Author Comment

by:Rayne
ID: 40369975
I meant for federal US Holidays
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40369977
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40369982
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Rayne
ID: 40369988
I need the federal US holiday from 2013 to 2020....so the fastest way to get them....just viewing will not help
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40369992
You can just prepare the relevant insert lines for all the years you need. Thanks
0
 

Author Comment

by:Rayne
ID: 40370028
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40370033
The point is you should keep such holiday table up to date, for all years. Thanks
0
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 50 total points
ID: 40370046
By yourself.
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 300 total points
ID: 40370103
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 40370707
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question