SQL Server - Spliting into weeks

Hi

I am passing input parameter as fromdate and todate.

I want to get the weeks between fromdate to todate.

Forexample,

If the fromdate='01/01/2011' and todate='03/30/2011'

WEEK 01Jan
WEEK 08Jan
WEEK 15Jan
.
.
WEEK 12FEB
WEEK 18FEB
.
.
WEEK 11MAR
WEEK 18MAR
WEEK 25MAR

..like this. How we can do in sql statement

Thanks

Just use this code

DateTime d1 =

new DateTime(2006,10,1);

DateTime d2 =

new DateTime(2007,10,15);

TimeSpan tt = d2-d1;

int totalWeeks = tt.Days/7;

MessageBox.Show(totalWeeks.ToString());

You simply get weekday and than add with your addition text what you want 'WEEK' + DATEPART(WEEKDAY, Date) + 'JAN' 

eg

DECLARE @starttime DATETIME, @endtime DATETIME
SET @starttime = '2011-05-20'
SET @endtime = '2011-06-30'

--Tally table would be better, for testing purposes I've included one here "on the fly"
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
t6 AS (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@starttime) AS startday
       FROM t4 x, t4 y),
tally AS (SELECT startday
          FROM t6
          WHERE startday <= @endtime
          AND startday = DateAdd(Week, DateDiff(Week, 0, startday), 0))
--Actual code
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS uniqid,
startday, DATEADD(DAY,4,startday) AS endday, DATEPART(WEEK,startday) AS weeknumber 
FROM tally

This helps you

Just use this code

 

DateTime d1 = new DateTime(2006,10,1);

DateTime d2 = new DateTime(2007,10,15);

TimeSpan tt = d2-d1;

int totalWeeks = tt.Days/7;

MessageBox.Show(totalWeeks.ToString());

 And if you want exact difference in fraction also then instead of

int totalWeeks = tt.Days/7;

use
double totalWeeks = tt.TotalDays/7;


Hope it helps

Here's a general solution which I believe should work for any choice of week starting and ending days. You could simplify it for your case, but this code gives you the option of changing the week's start and end (e.g. to Monday to Sunday) if it becomes necessary. It's not uncommon in payroll applications for the definition of a calendar week to change.

DateTime periodStart = new DateTime(2010, 10, 17);
    DateTime periodEnd = new DateTime(2010, 11, 14);
 
    const DayOfWeek FIRST_DAY_OF_WEEK = DayOfWeek.Monday;
    const DayOfWeek LAST_DAY_OF_WEEK = DayOfWeek.Sunday;
    const int DAYS_IN_WEEK = 7;
 
    DateTime firstDayOfWeekBeforeStartDate;
    int daysBetweenStartDateAndPreviousFirstDayOfWeek = (int)periodStart.DayOfWeek - (int)FIRST_DAY_OF_WEEK;
    if (daysBetweenStartDateAndPreviousFirstDayOfWeek >= 0)
    {
      firstDayOfWeekBeforeStartDate = periodStart.AddDays(-daysBetweenStartDateAndPreviousFirstDayOfWeek);
    }
    else
    {
      firstDayOfWeekBeforeStartDate = periodStart.AddDays(-(daysBetweenStartDateAndPreviousFirstDayOfWeek + DAYS_IN_WEEK));
    }
 
    DateTime lastDayOfWeekAfterEndDate;
    int daysBetweenEndDateAndFollowingLastDayOfWeek = (int)LAST_DAY_OF_WEEK - (int)periodEnd.DayOfWeek;
    if (daysBetweenEndDateAndFollowingLastDayOfWeek >= 0)
    {
      lastDayOfWeekAfterEndDate = periodEnd.AddDays(daysBetweenEndDateAndFollowingLastDayOfWeek);
    }
    else
    {
      lastDayOfWeekAfterEndDate = periodEnd.AddDays(daysBetweenEndDateAndFollowingLastDayOfWeek + DAYS_IN_WEEK);
    }
 
    int calendarWeeks = 1 + (int)((lastDayOfWeekAfterEndDate - firstDayOfWeekB

thankssssss a lottt..

You Welcome..............