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.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2007-12-07 : 20:29:52
|
| Dear all,I have an app that tracks all projects and captures those that have missed completion deadline.If any misses completion deadline, an email reminder is sent to the individual who is assigned that project.The following code picks up all projects that have missed deadline.The only issue with it is that it doesn't account for weekends.I was wondering if anyone knows how to determine that a project had missed a deadline by excluding weekends.This is what I have so far. I thank you in advance. SELECT tblProjects.AssignedTo, tblProjects.ProjName, tblProjects.StatusName, tblProjects.assignedToEmail, CONVERT(Char,StartDate,101), CONVERT(Char,finishDate,101) FROM tblProjects, tblEmployees WHERE tblEmployees.employeeFullName = tblProjects.EmployeeFullName AND tblProjects.StatusName = 'Past Due' AND CONVERT(Char,DateAdd(hour,-24,getdate()),101) > CONVERT(Char,finishDate,101) ORDER BY tblProjects.Project_Id DESC |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-12-08 : 09:43:09
|
| Michael Valentine Jones has contributed a whole lot of date related tips scripts and functions including this one:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=week,dayI suggest you look through the results of a sample function call and either use the function as is or "steal" some of the techniques he used. in your own code.Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Calculating dates without weakends