Web Development

General discussion


SQL Server 2k Determine Next Weeks Range

By parier ·
I am looking for a query that will,
upon executing, determine the date for next monday, even if today is monday, go to next monday.

This will eventually be for schedulers to determine whether or not employees are scheduled for next week.

This will be placed in a report, and users will have this as their "frontpage" and will let them know upon logging in, that they have employees that are not scheduled for next week.

The initial SQL (the one I am asking for in here) is what will start it all, then from these results, I can then pull deptartment/employees etc.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by lathubabu In reply to SQL Server 2k Determine N ...

here is the Query which will give you the Date for next Monday.

select NextMonDay_date= dateadd(day, case(datename(weekday,GETDATE()))
when 'Monday' then 7
when 'Tuesday' then 6
when 'Wednesday' then 5
when 'Thursday' then 4
when 'Friday' then 3
when 'Saturday' then 2
when 'Sunday' then 1
else 0
end ,getdate())

Hope this will do the needful.

Collapse -

by sjgbrown In reply to SQL Server 2k Determine N ...

An excerpt from:
How to Calculate Different SQL Server Dates
By Gregory A. Larsen


Monday of the Current Week

Here I use the week interval (wk) to calculate what date is Monday of the current week. This example assumes Sunday is the first day of the week.

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

If you don't want Sunday to be the first day of the week, then you will need to use a different method. Here is a method that David O Malley showed me that uses the DATEFIRST setting to set the first day of the week. This example sets Monday as the first day of the week, but by changing the DATEFIRST setting any day of the week could be the first day of the week.


select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

Collapse -

by MPrice1 In reply to SQL Server 2k Determine N ...

This answer overcomes the firstdate problem as suggested in answer 2

Select NextMonday = dateadd(day, DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())


Collapse -

by MPrice1 In reply to

After review a Modification was required.

Select NextMonday = dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())


Related Discussions

Related Forums