Web Development·11,946 discussions

SQL Server 2k Determine Next Weeks Range

Locked

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.

Topic

Reply To: SQL Server 2k Determine Next Weeks Range

In reply to SQL Server 2k Determine Next Weeks Range

Hi,
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.
Regards,
Lathesh
lathubabu@yahoo.co.uk

Reply To: SQL Server 2k Determine Next Weeks Range

In reply to SQL Server 2k Determine Next Weeks Range

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

http://www.databasejournal.com/features/mssql/article.php/3076421

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.

set DATEFIRST 1

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

Reply To: SQL Server 2k Determine Next Weeks Range

In reply to SQL Server 2k Determine Next Weeks Range

This answer overcomes the firstdate problem as suggested in answer 2

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

Cheers

Reply To: SQL Server 2k Determine Next Weeks Range

In reply to Reply To: SQL Server 2k Determine Next Weeks Range

After review a Modification was required.

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

Regards
Michael

[Author]

Replies