0

I create a query to get employees between two days before StartDate and two days after EndDate. It works fine for StartDate that falls on Tuesday and Thursday. It won't work right if these days fall on Friday to Monday. I don't want to calculate on Saturdays and Sundays.

Here is my query:

select
    FirstName, 
    LastName, 
    StartDate, 
    EndDate
from 
    Students
where 
    StartDate > GetDate()-2 
    AND EndDate < GetDate() +2

How do I change it to count weekdays only?

Thank you very much!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jenny Tran
  • 251
  • 5
  • 16
  • Potentially related: http://stackoverflow.com/questions/1803987/how-do-i-exclude-weekend-days-in-a-sql-server-query – AHiggins Nov 18 '15 at 18:23
  • If `GETDATE()` is a Friday, do you want to see Students who ended on the following Monday or Tuesday? – AHiggins Nov 18 '15 at 18:27
  • Woh, I have not thought about this. My main point is to list all Students who StartDate is less than 2 days from today's day and 2 days after today's day, without couting Weekends (Sat & Sun). Thanks. – Jenny Tran Nov 18 '15 at 18:37
  • So, just to confirm: a Student who starts on a weekend will never be listed by your query, no matter which day it runs; your query will return students from three days (if you run your query on Monday or Friday), four days (if you run on Tuesday or Thursday), or five days (if you run on Wednesday)? – AHiggins Nov 18 '15 at 18:41
  • Oh, I run the query every morning from Mon to Fri. I am trying to send a an alert message to all students who are 2 days before and 2 days after the current date. If a Student ends of Mon, he should receive this message on previous Friday. – Jenny Tran Nov 18 '15 at 19:05
  • You have a potential logic flaw here too. GETDATE returns a datetime. That means your start date would have to be no further back than exactly 48 hours from the current time. That means if you run this on Wednesday at 9am it will not return rows where the start date is Monday at 8:40am. I suspect that is not really what you want? – Sean Lange Nov 18 '15 at 19:40

1 Answers1

0

If I understand correctly, your start date can not be on a weekend. To exclude students who started on a weekend, add to your where clause AND datepart(dw, StartDate) not in (1,7).

select
    FirstName, 
    LastName, 
    StartDate, 
    EndDate
from 
    Students
where 
    StartDate > GetDate()-2 
    AND EndDate < GetDate() +2
    AND datepart(dw, StartDate) not in (1,7)

Hope this helps. Be sure to check the up arrow if this answer was useful to you!

Mike Zalansky
  • 796
  • 7
  • 14