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 (*) Defaultmon dd yyyy hh:miAM (or PM)
1101USAmm/dd/yy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106-dd mon yy
7107-mon dd, yy
8108-hh:mm:ss
-9 or 109 (*) Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd
-13 or 113 (*) Europe default + millisecondsdd mon yyyy hh:mm:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (*) ODBC canonicalyyyy-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)

*/