Business Days in GETDATE() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Business Days in GETDATE()

Author

Topic

mmendieta
Starting Member

2 Posts

Posted - 2010-01-04 : 14:48:12

Hi,

I have the following query:
------------------------------------
SELECT
Field1,
Field2,
DATE
FROM
TABLE_NAME
WHERE
DATE <= GETDATE()-2
------------------------------------
I need the "2" to be 2 Business Days. Please help!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-04 : 14:54:10

what is the datatype and content of table_name date field. remember getdate() has trailing hours min secs .....

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mmendieta
Starting Member

2 Posts

Posted - 2010-01-04 : 15:16:23

quote:
Originally posted by yosiasz

what is the datatype and content of table_name date field. remember getdate() has trailing hours min secs .....

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

the datatype is datetime. Contains date and time values.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-04 : 15:29:43

You can use Tan's code form here..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130526

Just give the number of days in the negative

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-04 : 16:10:21

I think this particular problem can be summarized in an easier fashion, since getdate() will be static for this and its in the where clause.

Just put a case condition on the <= part saying something like:

Case when DateName(dw, Getdate()) = 'Monday' then Date <= Getdate() - 4 
when DateName(dw, Getdate()) = 'Tuesday' then Date <=Getdate()- 3 
else Date <= GetDate() - 2

He only needs to look back 2 days from todays date, and there are only a few special cases. So if GetDate() was a Monday, you would only care about things that happened before on or before Thursday. Tuesday you would only look at on or before Friday etc....

There are only 3 cases to look at in this instance (NB: this does not take into account holidays)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources