Calendar and Business Date Functions/Calculations

Question

Post reply

Calendar and Business Date Functions/Calculations

Aimee Bachura

Grasshopper

Points: 16
More actions
May 16, 2007 at 3:18 pm

#173702

I need to calculate count of days for both business days and calendar days. Is there an easy way to do this?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

use the DateDif function. Refer to BOL.

Regards,
Matt

JacekO SSCertifiable Points: 6610 More actions · Answer 2

Do you need to deal with holidays as well or do you want to exclude Saturdays and Sundys only?

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 3

One way I've done it is to create a calendar table, like so:

CREATE TABLE dbo.CompanyCalendar (

CompanyDate smalldatetime,

Holiday char(1), -- 'Y' means it's a holiday like Christmas

BusinessDay char(1), -- 'Y' means it's a normal business day

WeekDay char(1), -- 'Y' means it's a weekday

WeekEnd char(1)) -- 'Y' means it's a Saturday or Sunday

I don't have the code handy, but you insert one row for each day in the next five years, then go back and manually indicate the holidays and business days. Write some code to set the WeekDay and WeekEnd flags. Then when you need to count the business days in a date range, just join with the calendar table.

There is no "i" in team, but idiot has two.

Aimee Bachura Grasshopper Points: 16 More actions · Answer 4

Hey all,

Matt,Thanks, but the datediff function only gives exact number of calendar days......I also need to calculate business days and calendar days excluding holidays.......

Thanks Dave - I think that sounds like a good solution!!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

If that's all you intend to use the Calendar table for, you can use the following along with a simple Holiday table for the same thing...

SELECT

(DATEDIFF(dd, @StartDate, @EndDate) + 1)

-(DATEDIFF(wk, @StartDate, @EndDate) * 2)

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JacekO SSCertifiable Points: 6610 More actions · Answer 6

Jeff,

I came up with somewhat similar code but I didn't have the + 1 in the first line. Otherwise there is 1 business day between today and today and I don't think this is right. But it might depend on the particular problem this number of business days code tries to solve.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Thanks for the feedback. Guess it's whatever you want. If today is a business day and the start date and end date are both today, I want it to register as 1 day. If you want that to be zero, instead, then just remove the +1 as you stated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aimee Bachura Grasshopper Points: 16 More actions · Answer 8

Thanks everyone - now here's a new twist to the equation....

I have a reational db that I'm reporting data from and need to have to business day count between a given date (say 01/01/2007) and today (getdate()).

How do I use the created table (either 5 year or holiday only) to match the date given (01/01/2007) from the database and complete the calculation?

JacekO SSCertifiable Points: 6610 More actions · Answer 9

Depending on your particular case you could do the following:

Create a table that will hold all the holidays

CREATE TABLE Holidays (holiday SMALLDATETIME)

Populate this table with all holidays that do not fall on the weekend.

Create a function like this:

CREATE FUNCTION fn_BusinessDays(@StartDate DATETIME, @EndDate DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @BusinessDays INT

SELECT @BusinessDays =

(DATEDIFF(dd, @StartDate, @EndDate))

-(DATEDIFF(wk, @StartDate, @EndDate) * 2)

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

SELECT @BusinessDays = @BusinessDays - COUNT(*)

FROM Holidays

WHERE holiday BETWEEN @StartDate - @EndDate

RETURN @BusinessDays

END

And then you can use this function in your select query or SP.

WARNING: Using this function might not be very efficient depending what information do you retrieve because this function will be called once for each record you retrieve. So if you retrieve thousands of records and each record needs the number of business days this function is going to be called thousand times.

And depeding on your requirements you may have to figure out if you want to use mine or Jeff's logic when figuring out the number of days.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeremy Wilhite SSC Rookie Points: 37 More actions · Answer 10

I think this logic is close, but the flaw that I spot is that if you have a 20 day span, this code only checks the FIRST and LAST day of the range to see if they are weekend days. The query does not know if the other 18 days are weekend or not.

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Not that anyone is waiting for an answer on this, since the last post was 5/22, but I will post my variation of this query when I finish it.

JacekO SSCertifiable Points: 6610 More actions · Answer 11

Jeremy,

I think the code works fine.

(DATEDIFF(dd, @StartDate, @EndDate))

-(DATEDIFF(wk, @StartDate, @EndDate) * 2)

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

The first line gives you the total difference in days. The second eliminates the weekends. The third and fourth are needed to clean up the start and end dates if they fall on the weekend. Try it.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeremy Wilhite SSC Rookie Points: 37 More actions · Answer 12

I just noticed the week count. was coming back to edit...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

By the way... if you want to know how JacekO's and my code works in some bit of detail, please refer to the following...

http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JacekO SSCertifiable Points: 6610 More actions · Answer 14

I have to do more reading of the stuff posted on this website. It would have saved me some time figuring out this function myself. My function was not as nice as Jeff's (I was originally using DATEPARTs instead of DATENAMEs) so I adopted his syntax.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]