Is it possible to convert a number (week number) back to date range?

[worldpeace]

The sql statement below return a week number (10)
select datename(week, '03/12/02') as number

Now is it possible to do the opposite, like I have a week number 10 then the statement(s) would return a date range from 03/03/02 to 03/09/02. Appreciate your helps.

 

[tlbroadbent]

This script returns the first and last day of the week for a week number.

Declare @wk int, @dt datetime

Select @dt='3/12/2002'
Select @wk=datepart(wk,@dt)

Select @dt=
dateadd(wk,@wk-1,'1/1/'+ltrim(str(year(getdate()))))

Select
FirstDate=dateadd(d,1-datepart(dw,@dt),@dt),
LastDate=dateadd(d,7-datepart(dw,@dt),@dt)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sunila7]

Hi,

This code also will return same result as Terry's

declare @date datetime
declare @FirstDate datetime
declare @SecondDate datetime
set @date = '3/12/02'

select @Firstdate=@date - datepart(dw, @date) + 1
select @SecondDate= @date-datepart(dw, @date) + 7
Select @FirstDate FirstDate,@Seconddate SecondDate


Sunil

 

[worldpeace]

Thanks Terry and Sunil, I appreciate your help.
I tried the codes and they worked great, but now I don't know which variable for me to change for the week number. What I wanted to do is if I enter a integer (1-53) as a week number then after execute the statements, they will return a date range.

for ex: if I enter 19 then 05/05/02 to 05/11/02 is returned and if I change the week number to 40 then 09/29/02 to 10/05/02 is returned.
Can this be done?

 

[tlbroadbent]

You can create a stored procedure that returns the two values based on the input number.

CREATE Procedure uspDateRangeOfWeek
@week int,
@FirstDate datetime output,
@LastDate datetime output
AS

Declare @dt datetime
Select @dt=
dateadd(wk,@week-1,'1/1/'+ltrim(str(year(getdate()))))
Select
@FirstDate=dateadd(d,1-datepart(dw,@dt),@dt),
@LastDate=dateadd(d,7-datepart(dw,@dt),@dt)
go

Example: Execute the procedure

Declare @d1 datetime, @d2 datetime
Exec uspDateRangeOfWeek 40, @d1 output, @d2 output
Print @d1
Print @d2

Result:

Sep 29 2002 12:00AM
Oct 5 2002 12:00AM Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MatthewPeters]

The following SP returns a string that looks like:

9/29/2002 TO 10/5/2002



Here's the SP. It's a modified version of Terry's. Hope you don't care Terry.

CREATE Procedure uspDateRangeOfWeek
@week int,
@retVal varchar output

AS

Declare @dt datetime
Declare @FirstDate datetime
Declare @LastDate datetime

Select @dt=
dateadd(wk,@week-1,'1/1/'+ltrim(str(year(getdate()))))

Select
@FirstDate=dateadd(d,1-datepart(dw,@dt),@dt),
@LastDate=dateadd(d,7-datepart(dw,@dt),@dt)

Select
CAST(datepart(mm, @FirstDate) AS VARCHAR) + "/" +
CAST(datepart(dd, @FirstDate) AS VARCHAR) + "/" +
CAST(datepart(yyyy, @FirstDate) AS VARCHAR) + " TO " +
CAST(datepart(mm, @LastDate) AS VARCHAR) + "/" +
CAST(datepart(dd, @LastDate) AS VARCHAR) + "/" +
CAST(datepart(yyyy, @LastDate) AS VARCHAR)


Here's how you call it:

Declare @retVal varChar
Exec uspDateRangeOfWeek 40, @retVal output
Print @retVal


Is this what you were looking for?

Matt

 

[tlbroadbent]

Why should I mind? I would simplify the string generation by using the convert function and date style 101.

Select
Convert(char(10), @FirstDate, 101) + ' TO ' +
Convert(char(10), @LastDate, 101) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MatthewPeters]

I like the simplified version better. I figured there had to be an easier way but didn't take the time to do a quick search on the BOL.

 

[worldpeace]

Thanks a lot for everyone's help.

Terri, I was trying to understand your 3 codes, especially the first one. Could you tell me shortly what these codes do to help me understand?

1. Select @dt=
dateadd(wk,@week-1,'1/1/'+ltrim(str(year(getdate()))))
why @week-1, and ltrim and str function?

Select
2. @FirstDate=dateadd(d,1-datepart(dw,@dt),@dt),
3. @LastDate=dateadd(d,7-datepart(dw,@dt),@dt)

I really appreciate your helps. THANKS

 

[tlbroadbent]

1) Why @week-1? When adding the week, you need to account for the fact that '1/1/yyyy' will be week 1 not week 0. Subtracting 1 yields the correct date for the week number.

2) Explain @FirstDate=dateadd(d,1-datepart(dw,@dt),@dt)
3) Explain @LastDate=dateadd(d,7-datepart(dw,@dt),@dt)

@dt will contain '1/1/yyyy' + the number of weeks. This date will be the same day of the week as Jan 1 of that year. In order to find the date of the first day of the week (Sunday) I take the day of week for @dt, subtract from 1 and add to @dt. If @dt falls on Wednesday the expression would be Dateadd(d,1-4,@dt) or Wednesday less three days which is Sunday.

Likewise the last day is 7-4 which equals Wednesday + 3 or Saturday.

Hope this helps.

-------------------------

I recommend that you join Tek-Tips rather than continuing to post as a visitor. It is free and there are several benefits to membership including email notification of replies to your posts and easy access to all posts that you have made. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.