Split weeks given year - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split weeks given year

Author

Topic

fkaratay
Starting Member

1 Post

Posted - 2011-01-07 : 03:01:25

Hi Dear !,

i want to select all weeks startdate and enddate given year.
for example;

my parameter is '2010'
i want to select all weeks startdate and enddate in this year

03-01-2011 / 09-01-2011
10-01-2011 / 16-01-2011
17-01-2011 / 23-01-2011
24-01-2011 / 30-01-2011
31-02-2011 / 06-02-2011
...
...
...

how can i do it ?
sorry for bad English

Thank you.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-07 : 04:38:34

Check if this works for you. It is just the start date of every week. Do same for the EndDate (your Assgnment) :D

Declare @date datetime
set @Date='20110103'
Declare @startDates table (sdate datetime)
While YEAR(@Date)<='2011'
Begin
Insert into @startDates
SElect @date
set @date=@date+7
End
SElect sdate from @startDates

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-01-07 : 05:12:38

Or try this:


DECLARE @year CHAR(4) = '2011';
DECLARE @date DATE = @year + '0101';
DECLARE @first_monday DATE = DATEADD(WEEK, 1 + DATEDIFF(WEEK, 0, DATEADD(DAY, -1, @date)), 0);

SELECT T.startdate, DATEADD(DAY, 6, T.startdate) AS enddate
  FROM (SELECT DATEADD(WEEK, V.number, @first_monday) AS startdate
          FROM master..spt_values AS V
         WHERE V.type = 'P'
           AND V.number <= 53) AS T
 WHERE T.startdate < DATEADD(YEAR, 1, @date);

vikky_2011
Starting Member

1 Post

Posted - 2011-01-07 : 05:30:43

hi,

DECLARE @var DATETIME

SELECT @var = '1/1/2011'

SELECT @var+ p.Number AS 'StartDate',@var+ p.Number+6 AS 'EndDate'
FROM master..spt_values AS P
WHERE P.type = 'p'
AND Type = 'P'
AND YEAR(@var+ p.Number) = 2011
AND YEAR(@var+ p.Number+6) = 2011
AND DATENAME(DW,@var+ p.Number) = 'Monday'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources