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 2000 Forums
 Transact-SQL (2000)
 Need a function for weekdays

Author  Topic 

michpaust
Starting Member

21 Posts
Posted - 2005-05-12 : 14:00:02
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
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2005-05-12 : 14:22:00
Try combining DATEDIFF with DATEPART
Go to Top of Page

michpaust
Starting Member

21 Posts
Posted - 2005-05-12 : 14:29:22
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 - 2005-05-12 : 14:38:10
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
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-05-12 : 14:42:43
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

X002548
Not Just a Number

15586 Posts
Posted - 2005-05-12 : 14:44:24
What about holidaze?





Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2005-05-12 : 14:48:47
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
Go to Top of Page

michpaust
Starting Member

21 Posts
Posted - 2005-05-12 : 14:57:56
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
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-05-12 : 14:58:21
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 - 2005-05-12 : 15:09:51
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

7423 Posts
Posted - 2005-05-12 : 15:12:32
not yet .....

- Jeff
Go to Top of Page

nitinblr
Starting Member

3 Posts
Posted - 2005-06-08 : 04:23:50
thanks very much jeff!!! that was a real help!!
Go to Top of Page

swatch89
Starting Member

2 Posts
Posted - 2005-11-18 : 14:27:40
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

7423 Posts
Posted - 2005-11-18 : 16:02:53
.... 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 - 2005-11-18 : 16:46:14
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

407 Posts
Posted - 2005-11-21 : 12:55:26
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.
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-11-23 : 08:14:47
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

7 Posts
Posted - 2005-11-23 : 08:41:28
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

7423 Posts
Posted - 2005-11-23 : 09:17:10
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 Post
Posted - 2009-09-14 : 09:54:34
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site