How do you calculate the number of weeks between two dates?

Question

How do you calculate the number of weeks between two dates?

for example as follows

Declare @StartDate as DateTime = "01 Jan 2009"; Declare @EndDate as DateTime = "01 June 2009"; @StartDate and @EndDate 

Answer 1

Use the Datediff function. datediff(ww,@startdate,@enddate)

ww tells the function which unit you need the difference to consider.

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Answer 2

You can use the following function to search for a week between two dates:

 CREATE FUNCTION [dbo].[fGetWeeksList] ( @StartDate DATETIME ,@EndDate DATETIME ) RETURNS TABLE AS RETURN ( SELECT DATEADD(DAY,-(DATEPART(DW,DATEADD(WEEK, x.number, @StartDate))-2),DATEADD(WEEK, x.number, @StartDate)) as [StartDate] ,DATEADD(DAY,-(DATEPART(DW,DATEADD(WEEK, x.number + 1, @StartDate))-1) ,DATEADD(WEEK, x.number + 1, @StartDate)) AS [EndDate] FROM master.dbo.spt_values x WHERE x.type = 'P' AND x.number <= DATEDIFF(WEEK, @StartDate, DATEADD(WEEK,0,CAST(@EndDate AS DATE)))