SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need a function for weekdays
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

michpaust
Starting Member

21 Posts

Posted - 05/12/2005 :  14:00:02  Show Profile  Reply with Quote
Does anyone know what function I would use to find the difference between two dates but will only include Monday to Friday in its calculation? I have already used the datediff function to get the result as an integer but now I need to take Sat and Sunday out from my result. Thanks!

--I don't suffer from insanity. I enjoy every minute of it.--

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/12/2005 :  14:22:00  Show Profile  Reply with Quote
Try combining DATEDIFF with DATEPART
Go to Top of Page

michpaust
Starting Member

21 Posts

Posted - 05/12/2005 :  14:29:22  Show Profile  Reply with Quote
How exactly would that work? I know datepart would return a number that correspods to the day of the week. Here's an example of what I am looking for
datediff(day,xx/xx/2004,yy/yy/2003)= n days. I need to subtract the saturdays and sundays from that result n . How would incorporating datepart in there help. I'm sorrt but I just can't form the syntax in my head.

--I don't suffer from insanity. I enjoy every minute of it.--
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/12/2005 :  14:38:10  Show Profile  Reply with Quote
Thanks for reminind me..I forgot I wanted to blog this awhile ago

http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx



Brett

8-)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/12/2005 :  14:42:43  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
if there are no holidays to consider:

the blue part is the real calc. the rest is showing what the steps are.

Declare @aDate datetime,
	@bDate datetime
Set @aDate = '4/22/2005'
Set @bDate = '5/12/2005'

Select 
	dayDiff = datediff(dy,@aDate,@bDate),
	fullweeks = datediff(dy,@aDate,@bDate)/7,
	weekdays = datediff(dy,@aDate,@bDate)/7*2,
	startday = datepart(dw,@aDate),
	extraDays = datediff(dy,@aDate,@bDate)%7,
	estraWeekDays = case 
			when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
			else 0 end,
	weekdayDiff = datediff(dy,@aDate,@bDate) - (datediff(dy,@aDate,@bDate)/7*2 + 
			case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
			when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7=6 then 2
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
			else 0 end)


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Edited by - Seventhnight on 05/12/2005 14:44:09
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/12/2005 :  14:44:24  Show Profile  Reply with Quote
What about holidaze?





Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  14:48:47  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
if the start date and end date are both weekdays, then the total # of week days in between is simply:


(total difference in days) - (total difference in weeks) * 2

or

 DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2


Since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.

If you have a table of holdays, then you simply substract them out as well:

 DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2 - 
 (select count(*) from holidays where holiday_date between @start and @end)


- Jeff

Edited by - jsmith8858 on 05/12/2005 14:51:52
Go to Top of Page

michpaust
Starting Member

21 Posts

Posted - 05/12/2005 :  14:57:56  Show Profile  Reply with Quote
Yes I forgot to take holidays into account also. Thanks to all for all the help.

--I don't suffer from insanity. I enjoy every minute of it.--
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/12/2005 :  14:58:21  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
well brett... i did say if holidays are not an issue.

but i like jeffs solution. i didn't realize that the datediff(ww,...) counted crossed boundaries.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/12/2005 :  15:09:51  Show Profile  Reply with Quote
Neat....I can lose the weekend population...

And Jeff, I like the drunk photo...but now that you've officialy changed it....does that mean your status is chamging?



Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  15:12:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
not yet .....

- Jeff
Go to Top of Page

nitinblr
Starting Member

3 Posts

Posted - 06/08/2005 :  04:23:50  Show Profile  Send nitinblr a Yahoo! Message  Reply with Quote
thanks very much jeff!!! that was a real help!!
Go to Top of Page

swatch89
Starting Member

2 Posts

Posted - 11/18/2005 :  14:27:40  Show Profile  Reply with Quote
This code doesn't work if the difference between the start date and end date is greater than or equal to 7, e.g. @aDate = '11/1/2005',
@bDate = '11/18/2005'. After trying to analyze your code and try to figure out what was wrong with it, I decided to create my own function instead:

CREATE function dbo.DateDiffWeekdays
(@fromdate datetime,
@todate datetime)
RETURNS int
AS
begin
declare @procdate datetime, @enddate datetime
declare @weekdays int
set @procdate = @fromdate
set @weekdays = 0

while (@procdate < @todate)
begin
if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7)
set @weekdays = @weekdays + 1
set @procdate = dateadd(d, 1, @procdate)

end
---
if @todate is null
set @weekdays = null

return @weekdays
end





quote:
Originally posted by Seventhnight

if there are no holidays to consider:

the blue part is the real calc. the rest is showing what the steps are.

Declare @aDate datetime,
	@bDate datetime
Set @aDate = '4/22/2005'
Set @bDate = '5/12/2005'

Select 
	dayDiff = datediff(dy,@aDate,@bDate),
	fullweeks = datediff(dy,@aDate,@bDate)/7,
	weekdays = datediff(dy,@aDate,@bDate)/7*2,
	startday = datepart(dw,@aDate),
	extraDays = datediff(dy,@aDate,@bDate)%7,
	estraWeekDays = case 
			when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
			else 0 end,
	weekdayDiff = datediff(dy,@aDate,@bDate) - (datediff(dy,@aDate,@bDate)/7*2 + 
			case when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7<6 then 1
			when datepart(dw,@aDate)=1 and datediff(dy,@aDate,@bDate)%7=6 then 2
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)=7 then 1
			when datepart(dw,@aDate)>1 and datepart(dw,@aDate)+datediff(dy,@aDate,@bDate)>7 then 2
			else 0 end)


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/18/2005 :  16:02:53  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
.... or you could use the 1-line solution I gave if you want to keep things simple ...
Go to Top of Page

swatch89
Starting Member

2 Posts

Posted - 11/18/2005 :  16:46:14  Show Profile  Reply with Quote
Your solution doesn't work if the start date falls on a weekend and the end date falls on a weekday. For example:

declare @start smalldatetime, @end smalldatetime
set @start = '11/5/05'
set @end = '11/9/05'
print DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2

The correct number of days should be 3, not 2. My function handles this case correctly :Þ


quote:
Originally posted by jsmith8858

.... or you could use the 1-line solution I gave if you want to keep things simple ...

Go to Top of Page

igorblackbelt
Constraint Violating Yak Guru

USA
407 Posts

Posted - 11/21/2005 :  12:55:26  Show Profile  Click to see igorblackbelt's MSN Messenger address  Reply with Quote
I've been using a function we found on the web a while ago an customized to our needs, it might look a little silly but it works for us, it counts a number of days between DateField1 and DateField2, which we use for measurement reporting.

CREATE FUNCTION dbo.[GetWorkingDays]
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
DECLARE @HolidayDifference int

IF (DATEDIFF(day, @StartDate, @EndDate) <1)
BEGIN
RETURN ( 1 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate),
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 6
WHEN 'Tuesday' THEN 5
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 3
WHEN 'Friday' THEN 2
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 0
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays+1
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN @LastPart
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END

SELECT @HolidayDifference = (SELECT COUNT(nas.dbo.tblholidays.holidayname) AS CountOfHolidays
FROM nas.dbo.tblholidays
where nas.dbo.tblholidays.holidaydate >= @StartDate
AND nas.dbo.tblholidays.holidaydate <= @EndDate)

SELECT @WorkDays = @WorkDays - @HolidayDifference

-- End Modification

RETURN ( @WorkDays )
END



---

Thanks!
Igor.

Edited by - igorblackbelt on 11/21/2005 12:57:42
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/23/2005 :  08:14:47  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
quote:
Originally posted by swatch89

Your solution doesn't work if the start date falls on a weekend and the end date falls on a weekday. For example:

declare @start smalldatetime, @end smalldatetime
set @start = '11/5/05'
set @end = '11/9/05'
print DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2

The correct number of days should be 3, not 2. My function handles this case correctly :Þ


quote:
Originally posted by jsmith8858

.... or you could use the 1-line solution I gave if you want to keep things simple ...





In his post... he did specify that the begin and end were weekdays, so you would have to make allowances for the other cases... maybe shift the days to a weekday?

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

sqlmember
Starting Member

Pakistan
7 Posts

Posted - 11/23/2005 :  08:41:28  Show Profile  Send sqlmember a Yahoo! Message  Reply with Quote
To make the Jeff's line of code accurate I have made the following change

select DateDiff(dd, @start-1, @end) - DateDiff(ww, @start, @end)*2

-Khurram Iqbal
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/23/2005 :  09:17:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by sqlmember

To make the Jeff's line of code accurate I have made the following change

select DateDiff(dd, @start-1, @end) - DateDiff(ww, @start, @end)*2

-Khurram Iqbal



To make it accurate for what situation? this returns 1 if the start and end date are the same day. Surely there are not any weekdays between 11/23/2005 and 11/23/2005 ?

If the start day or the end day might be on a weekend, then you need to define what to do in those situations in your requirements. To me, it doesn't make sense to allow for this, but if you want to, simply define what you wish to do in that situation and handle it.

For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many weekdays are between those dates? 0 or 1 or "undefined" (null) ? You must define this. It can be interpreted in any of those ways.

Go to Top of Page

Andy123
Starting Member

1 Posts

Posted - 09/14/2009 :  09:54:34  Show Profile  Reply with Quote
Hi,

I read through this post looking for code I could use as a column in an SQL query and ended up making my own formula which I thought may be useful to share:

Where DateA is before DateB:

(DateB-DateA) - FLOOR((DateB-DateA)*2/7) - IF(DOW(DateA) <= DOW(DateB),0,2)

Explanation:
(DateB-DateA) -> no of calendar days

- FLOOR((DateB-DateA)*2/7) ->minus 2 days for every whole week

- IF(DOW(DateA) <= DOW(DateB),0,2) -> minus another 2 days only if the day of the week of A is not before the day of the week of B. I.E. if its mon to thurs this will return '-0' if its thursday to mon it will return '-2'

This does not account for bank holidays, but it does the basic job.


Andy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000