Get week information (beginning, end) for the date

IF EXISTS (SELECT name FROM sysobjects 
			WHERE name = '_WeekByDate_sp' AND type = 'P')
	DROP PROCEDURE _WeekByDate_sp
GO

create procedure _WeekByDate_sp
		@currdate varchar(25),
		@Format int = 101	
as
/*
The following procedure will get week information ( number,day start-day finish) for requested date.
Format of the output dates could be changed. (see 'style' info in the SQL HelpFile for 'Convert' function)


Without   With 
century century 
(yy)	(yyyy)	Standard Input/Output**
-	0 or 100 (*) 	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106	-	dd mon yy
7	107	-	mon dd, yy
8	108	-	hh:mm:ss
-	9 or 109 (*) 	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*) 	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (*) 	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (*) 	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)


Created by Eva Zadoyen
04/08/2002 
*/

declare @firstday varchar(25)
declare @endday varchar(25)
declare @day int
declare @week int

select @week = datepart(ww,@currdate)
select @day =(datepart(dw,@currdate)+6 -@@datefirst)* (-1)
select @firstday = convert(varchar(25),dateadd( dd,@day, @currdate),@format)
select @endday = convert(varchar(25),dateadd(dd,7,@firstday),@format)
select @currdate 'Date',@week 'Week', @firstday 'First Day of the Week',@endday 'Last Day of the Week' 

/*

declare @mydate varchar(25)
set @mydate = '4/22/2002'
exec _weekByDate_sp @mydate,110

Date                      Week        First Day of the Week     Last Day of the Week      
------------------------- ----------- ------------------------- ------------------------- 
4/22/2002                 17          04-21-2002                04-28-2002

(1 row(s) affected)

*/