Calculate DateDiff Excluding Weekends in SQL or UDF Run on UNTRUSTED Location

[Marcifish]

Hello;

I am working on a small database and need to calculate differences between dates excluding weekends. I have found a large number of posts regarding doing this with VBA functions, but I need to do it strictly in SQL (it needs to feed directly into query results on a database whose location cannot be set as trusted due to my company's network requirements/regulations).

If anyone knows of a UDF that will perform this function without the database being stored in a trusted location, I could use that instead, but all of the UDF's I can find in the forum require a trusted location to run.



Any help would be greatly appreciated

[alansidman]

Use the weekday interval "w" in the DateDiff function. Create a new field or column using SQL

http://beginner-sql-tutorial.com/sql...-statement.htm

and

http://www.techonthenet.com/access/f...e/datediff.php

Alan

[Marcifish]

Excellent!

I didn't even need to use the insert function, I was simply able to replace the "d" in my existing datediff functions in the primary query's expressions with "w." I swear, both of the books I have didn't even mention that as an option in calculated fields... I can't believe it was that easy!

Thanks so much!

[RuralGuy]

I'm sorry but I do not see how using a "w" interval would give you workdays between two dates. It gives you weeks but not workdays. I'm willing to learn here.

[alansidman]

Allan;
"ww" is for week
"w" is for weekday

Alan

[RuralGuy]

Alan,
I knew that and tried the "w" in the Immediate window to make sure. I believe it will tell you how many "for example Wednesdays" betweem two dates.

[alansidman]

Allan;
Thanks for pointing that out. I had not tested it using "w," but had used it with Day and Month. I misinterpreted. I just tested and of course you are right. My turn to learn. Thanks again.. So now I wonder how the OP solved his problem?

Alan

[RuralGuy]

My thoughts as well. I changed the thread to "Unsolved" since that was misleading.