SQL Server - Spliting into weeks - Asked By Anandh Ramanujam on 20-Dec-11 07:03 AM

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
Suchit shah replied to Anandh Ramanujam on 20-Dec-11 07:10 AM

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());

Web Star replied to Anandh Ramanujam on 20-Dec-11 07:12 AM
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
Suchit shah replied to Anandh Ramanujam on 20-Dec-11 07:12 AM

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


Anoop S replied to Anandh Ramanujam on 20-Dec-11 07:18 AM

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

Anandh Ramanujam replied to Web Star on 20-Dec-11 08:33 AM
thankssssss a lottt..
Web Star replied to Anandh Ramanujam on 20-Dec-11 11:39 AM
You Welcome..............