Solved

TSQL Date?

Posted on 2014-10-08
10
215 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
[X]
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
10 Comments
 

Author Comment

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

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 11

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 11

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 11

Expert Comment

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

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 66

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

624 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