add business day to a existing date

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

General SQL Server Forums

New to SQL Server Programming

add business day to a existing date

Author

Topic

Thierry
Starting Member

4 Posts

Posted - 2008-06-25 : 12:49:18

Hi All,

In my query, I have a column giving date&time expressed in calendar days like below

2008-03-31 00:00:00.000

What I would like to do is to add to this data a certain number of days expressed in business days meaning that if it falls on a w-e, it is taken into account and it goes to the next business day.

Could you someone help me on this

Thanks

Thierry

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 13:39:04

Using SQL Server 2005 or SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

Thierry
Starting Member

4 Posts

Posted - 2008-06-26 : 03:37:27

Using SQL Server 2005.

By the way, I will reformulate my question. I would like to know how I can make a condition like below

X date = Today - 6 business days where x date is expressed as below

2008-06-24 17:05:52.487

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 03:54:27

one method:-

DECLARE @EndDate datetime,@StartDate datetime
SELECT @EndDate='25 Oct 2007',@StartDate='25 Oct 2008'--example dates

SELECT DATEDIFF(dd,MIN(Date),@StartDate)- SUM(Counter)
FROM
(
SELECT DATEADD(dd,-1*number,@StartDate) AS Date,
CASE WHEN DATEPART(dw,DATEADD(dd,-1*number,@StartDate)) IN (1,7) THEN 1 ELSE 0 END AS Counter
FROM master..spt_values
where type='p'
and DATEADD(dd,-1*number,@StartDate)>@EndDate)t

you can use this for dates at a maximum difference of 5 years

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 09:59:37

or

SELECT count(*) -1
FROM
(
SELECT DATEADD(dd,-1*number,@StartDate) AS Date
FROM master..spt_values
where type='p'
and DATEADD(dd,-1*number,@StartDate)>@EndDate
and DATENAME(weekday,DATEADD(dd,-1*number,@StartDate)) not in ('saturday','sunday')
) as t

Madhivanan

Failing to plan is Planning to fail

Thierry
Starting Member

4 Posts

Posted - 2008-06-30 : 08:11:05

Thanks for the above. Thanks to your query, I have the number of business days. What I would like to have is a condition where it gave me the exact date

example:

I based myself on 1 information, my order date. I would like to have query listing all order dates which are today minus 6 business days.

I don't know how to express today - 6 business days.

Thanks in advance for your help.

Thierry

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-30 : 09:43:11

What date would you like to return if TODAY - 6 BUSINESS DAYS are on a saturday?
What date would you like to return if TODAY - 6 BUSINESS DAYS are on a sunday?

E 12°55'05.25"
N 56°04'39.16"

Thierry
Starting Member

4 Posts

Posted - 2008-06-30 : 09:47:28

If for example today - 6 business days is equal to saturday, the query would go to the previous friday. For Sunday, it would also go to the previous friday.

Hope it helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources