>declare @dt datetime >declare @dt1 datetime >declare @dt2 datetime >declare @dt3 datetime >declare @dt4 datetime >declare @dt5 datetime > >set @dt1 = '06/01/2007' -- First Week >set @dt2 = '06/04/2007' -- Second Week >set @dt3 = '06/11/2007' -- Third Week >set @dt4 = '06/18/2007' -- Fourth Week >set @dt5 = '06/25/2007' -- Fifth Week > >select >ceiling(cast((cast(DAY(@dt1) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week1, >ceiling(cast((cast(DAY(@dt2) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week2, >ceiling(cast((cast(DAY(@dt3) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week3, >ceiling(cast((cast(DAY(@dt4) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week4, >ceiling(cast((cast(DAY(@dt5) as decimal(10,5))-1)/7 as decimal(10,5))+1) as Week5 > >Week1 Week2 Week3 Week4 Week5 >=================================== ======= >1 2 3 4 5 > > >