Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TSQL Date?

Posted on 2014-10-08
10
Medium Priority
?
218 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 200 total points
ID: 40370046
By yourself.
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 1200 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 600 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

926 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