datediff in expression excluding weekends

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

Development Tools

Reporting Services Development

datediff in expression excluding weekends

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 09:57:06

Hello, I have the following expression:

=Iif((Fields!DELIVERYDATE.Value > Fields!SHIPPINGDATEREQUESTED.Value),
datediff("w",Fields!DELIVERYDATE.Value,Fields!SHIPPINGDATEREQUESTED.Value), "NotLate")

How do I properly subtract these two dates to ignore weekends? I've researched online and many solutions were to write a UDF to calculate the weekends and call within the query first but there has to be an easier way using an expression...?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:11:26

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:26:12

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Can you lead me to the best UDF I can use. Also, how to call the UDF within my reports query. I can post my query if need be.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:32:20

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Can you lead me to the best UDF I can use. Also, how to call the UDF within my reports query. I can post my query if need be.

Thank you.

see one below
http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

use @weekdays = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:33:00

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:34:45

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?

you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:40:47

quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?

you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 10:45:08

quote:
Originally posted by visakh16

quote:
Originally posted by SQLSoaker

quote:
Originally posted by visakh16

nope. you need to use udf or write custom code in report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Also, can't I just use datepart together with datediff directly in my query instead of going thru the trouble of writting a UDF?

you mean in query behind?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, either in the query behind or within the expression, either or.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:48:51

try something like


SELECT DATEDIFF(dd,StartDate,EndDate) - (2 * DATEDIFF(wk,StartDate,EndDate)) as Weekdays FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 11:01:25

Wow, worked like a charm!!! So how does this logic go? If I am following correctly, you get an integer number of days using DATEDIFF(dd,StartDate,EndDate) and then you get an integer number of weeks using DATEDIFF(wk,StartDate,EndDate), multiply by 2 and subtract the two integers... I guess I don't get this part: (2 * DATEDIFF(wk,StartDate,EndDate))

None the less, it works great, thank you again visakh!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 11:05:40

you've 2 weekend days per week

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2010-04-22 : 11:09:28

I see, you da man.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:44:51

cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources