OK, so did you *try* the query I posted? It won't return a weekend
regardless of what day you run it on. And "the date" I was talking about
was the date that resulted from the calculation in your query, not the day
you run the query. The problem with your query is that you just returned a
single day where the date was n days out, and since you added IsWeekday = 1,
you go down to the empty set if n days out is a weekend. If you want the
NEXT weekday then:
DECLARE @Date SMALLDATETIME,
@Date = DATEIFF(DAY, 0, CURRENT_TIMESTAMP),
@Days = 5;
SELECT TOP 1
[Today] = @Date
[DT] >= DATEADD(DAY, @Days, DATEDIFF(DAY, 0, @Date))
AND IsWeekDay = 1
If neither of these queries do what you want, then you'll need to explain
better what you want. And I don't mean more narrative, I mean give me some
values for @Date and @Days, and tell me what you want returned in each case.
For example, if what you really want is for @Days to represent WEEKDAYS
ONLY, e.g. 5 weekdays from now, then you'll need to tell us that.
I wasn't thinking this would be run on a weekend. I was going to
schedule this to only run on week days.
I would say that if this runs on a Saturday and we're looking out 5
days with it then it should return the next Thursday.
It should always only go in one direction, forward by passing it a
positive number of days, or backword by passing it a negitive number
of days. If the day is Saturday and I pass a positive 1, plus one from
today, I think it would be better to have it return Monday. In any
case I was planning on this only running on week days and so it should
only return week days.
I'll add to this that the reason for doing it this way is that
weekends are being chosen by the business as non-business days and
should never be included.
On Jun 10, 3:41 pm, "Aaron Bertrand [SQL Server MVP]"