Friday, January 23, 2009

Access 2003 Using DateDiff for "Business Days" or Weekdays without using outside module

I see this problem alot among developers and I just thought id share some info. If you are using a very simple DBMS like Access 2003 and can't extend functionality to your standalone application, because access 2003 doesn't let you use UDF (User defined functions) outside of itself. Here is how I came up with determing the amount of WEEKDAYS (M-F) between two dates without having to create a UDF in the DB.
SELECT  * FROM tblExample WHERE datediff('ww',[fldDate],Date(),2) + datediff('ww',[fldDate],date(),3) + datediff('ww',[fldDate],Date(),4) + datediff('ww',[fldDate],Date(),5) + datediff('ww',[fldDate],Date(),6) >= 5
That will return all records where fldDate and Date() are greater than or equal to 5 weekdays. Of course if you can use custom functions in your DBMS, then you wont need this.