General discussion

Locked

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.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

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

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

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

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())

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())

Cheers

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())

Regards
Michael

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums