Calculate a Future Date

Many reports I create are roll sheets or other date-specific reports that need to print a future date, usually the "next Sunday" after "today." Let's walk through the process of finding the "next Sunday" on the calendar.

First, remember the function to return today's date:

select Today = getdate()

Second, remember that we can move the date into the future by adding a value equal to the number of days we want to move. Thus, the date for tomorrow would be calculated this way:

select Tomorrow = getdate() + 1

Third, it is helpful to keep in mind that each day of the week is assigned a digit from 1 (for Sunday) through 7 (for Saturday). The SQL statement that returns the current day-of-the-week digit value is:

select DayOfTheWeekDigit = datepart(dw, getdate())

Thus, what we need to figure out is "How many days after today will be next Sunday?" Obviously, if we are asking that question on Sunday the answer is seven. If we are asking that question on Monday the answer is six, on Tuesday it is fix, and so on through Saturday when it is one. Applying some basic math concepts, we can eventually derive the following formula:

"Next Sunday" = "Today" + (8 - "Today's Day of the Week Digit")

Substituting the acutal SQL syntax for this formula we have:

select NextSunday = getdate() + (8 - datepart(dw, getdate()))

Simply changing the 8 to a 9 will calculate next Monday, a 10 will calculate next Tuesday, and so on. By adjusting this formula, you can calculate any future day of the week.