Solved

TSQL Date?

Posted on 2014-10-08
10
204 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how the fundamental information of how to create a table.

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now