Datetime manipulation question

[sonname]

I need to do some date manipulations in sql. I can do this within a stored procedure. Here's my question.
Given a week of Monday to Friday, If today is Tuesday October 4th, I need to find out what day was Monday i.e. October 3rd and what day will be Friday i.e October 7th. Basically I need to find out the Monday and Friday dates given a particular day during that week. Thanks in advance.

 

[bborissov]

SET DATEFIRST 1 && The first day of week is Monday
DECLARE @dd as datetime
SET @dd = GetDate()
SELECT dateadd(day,1-datepart(dw,@dd),@dd) && Get Monday date
SELECT dateadd(day,5-datepart(dw,@dd),@dd)&& Get Friday date

Borislav Borissov

 

[amrita418]

If you are looking for a statement that dymically calculates returns Monday, Friday dates then try this:

DECLARE @dd datetime
SET @dd = '09-26-2005'

select case when datepart(dw, @dd) = 2 then convert(varchar(10), @dd, 101)
            when datepart(dw, @dd) = 3 then convert(varchar(10), @dd - 1, 101)
            when datepart(dw, @dd) = 4 then convert(varchar(10), @dd - 2, 101)
            when datepart(dw, @dd) = 5 then convert(varchar(10), @dd - 3, 101)
            when datepart(dw, @dd) = 6 then convert(varchar(10), @dd - 4, 101) 
            else 'Not WeekDay' end 'DateMonday',
       case when datepart(dw, @dd) = 2 then convert(varchar(10), @dd + 4, 101)
            when datepart(dw, @dd) = 3 then convert(varchar(10), @dd + 3, 101)
            when datepart(dw, @dd) = 4 then convert(varchar(10), @dd + 2, 101)
            when datepart(dw, @dd) = 5 then convert(varchar(10), @dd + 1, 101)
            when datepart(dw, @dd) = 6 then convert(varchar(10), @dd, 101) 
            else 'Not WeekDay' end 'DateFriday'

Regards,
AA

 

[MDXer]

Using Set DateFirst the below code works

Declare @tmpDate datetime
Declare @FirstDow int
Declare @WeekStart datetime
Declare @WeekEnd datetime

Set @tmpDate = '10/04/05'

Set @FirstDow = 1
Set DateFirst @FirstDow

Set @WeekStart = DateAdd(dd,((@@DATEFIRST)-(DatePart(dw,@tmpDate))),@tmpDate)
Set @WeekEnd = DateAdd(dd,(7-(DatePart(dw,@tmpDate))),@tmpDate) - 2

Print @WeekStart
Print @WeekEnd

if you want the end of the week you remove the -2

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[rafrancisco]

You can find in the following link a function that will return the first day of the week for the given date parameter. The output depends on what is set as the first day of the week (@@DATEFIRST)

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

To use it to get Monday, if the first day of the week is Sunday, then just add 1 to it. To get Friday, then add 5 to it, as follows:

SELECT [dbo].[ufn_GetFirstDayOfWeek] ( GETDATE() ) + 1 AS [Monday], 
       [dbo].[ufn_GetFirstDayOfWeek] ( GETDATE() ) + 5 AS [Friday]

Hope this helps.