exclude holidays in sql

I want to exclude holiday from my select query. I have a holiday table. How can I exclude it?

2014-01-01 00:00:00.000      Holiday         New Years Day
2014-05-26 00:00:00.000      Holiday         Memorial Day
2014-07-04 00:00:00.000      Holiday         Independence Day
2014-09-01 00:00:00.000      Holiday         Labor Day
2014-11-27 00:00:00.000      Holiday         Thanksgiving
2014-12-25 00:00:00.000      Holiday         Christmas
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It would be helpful if you provide the original query but you can do this with an EXCEPT or with a JOIN depending on what you are trying to do.
Brendt HessSenior DBACommented:
FROM WhateverTable as t
LEFT JOIN Holidays as h
    ON t.SomeDate >= h.Date
    AND t.SomeDate < Dateadd(day, 1, h.Date)
WHERE h.date is null
Scott PletcherSenior DBACommented:
I suggest NOT EXISTS, as that allows SQL to generate the best plan:

FROM main_table mt
    NOT EXISTS(SELECT 1 FROM holidays h WHERE h.date = mt.some_date)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

VBdotnet2005Author Commented:
What does Select 1 mean?
HuaMin ChenProblem resolverCommented:
I suggest you have a holiday table to store the relevant and within your column, you can add such condition
where date_col not in(select holiday from holidaytab)

Open in new window

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like the above experts have the T-SQL covered given an already existing holiday table.  

If you're interested, SQL Server does not have an ISHOLIDAY() function to programmatically define a holiday, and even if that were possible it wouldn't account for country, state, and local differences.   I have an article out there called Build your own SQL calendar table to perform complex date expressions that walks you through creating your own date table where you can programmatically define what is a holiday and what isn't.
Anthony PerkinsCommented:
I suggest you have a holiday table to store the relevant
You may want to read the question:
I want to exclude holiday from my select query. I have a holiday table.
Anthony PerkinsCommented:
What does Select 1 mean?
It means just what it states: Return the literal 1.  In much the same way you do SELECT GETDATE() to return the value of the current date time of the system.

But to answer your question (or where I think you were heading with it) "Why do you use SELECT 1?" and that would be because traditionally it is a considered to be far less expensive to do SELECT 1 compared to say SELECT ColumnName or God forbid SELECT * and the result is exactly the same.  Having said that I suspect the SQL Query Optimizer is smart enough to know that if you did use SELECT * in an EXISTS statement, it would probably resolve it to something closer to SELECT 1.  But Scott can probably elaborate better on that.
Scott PletcherSenior DBACommented:
>> What does Select 1 mean? <<

It's just the simplest SELECT list possible.  All we need to do is check whether or not a row exists, not return any column(s), so the value in the SELECT list is not meaningful, and "SELECT 1" shows that nicely and simply.
Vikas GargBusiness Intelligence DeveloperCommented:

Try this

SELECT * from table
    transdate not in (SELECT date FROM holidays)

Open in new window

I agree with ScottPletcher that NOT EXISTS is the way to go. I will say that there is some debate as to using select 1 or select null as being the simplest. I think it may depend on the dbms and the difference in any case is likely to be negligible.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.