How can i Get the week number of year

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

SQL Server 2005 Forums

Transact-SQL (2005)

How can i Get the week number of year

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2010-04-12 : 07:41:56

Hi,

how i can get the week list of year. For example if i pass 2010 as year it should return me 53 week and start and end date of week.

Week Start-Date End-Date
01 01-Jan-2010 07-Jan-2010
02 08-Jan-2010 15-Jan-2010

and so on.

thx.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-12 : 07:59:32

make use of F_TABLE_DATE

group by the week no and find min / max of the date

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 08:01:53

[code]
DECLARE @Year int
SET @year=2010

;With Year_Calendar([Week],Start,[End])
AS
(
SELECT DATEPART(wk,DATEADD(yy,@year-1900,0)),DATEADD(yy,@year-1900,0),DATEADD(yy,@year-1900,0)+7
UNION ALL
SELECT DATEPART(wk,DATEADD(wk,1,Start)),DATEADD(wk,1,Start),DATEADD(wk,1,[End])
FROM Year_Calendar
WHERE DATEADD(wk,1,[End])< DATEADD(yy,@year-1899,0)
)

SELECT *
FROM Year_Calendar
OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-12 : 08:12:06

[code]DECLARE @theYear SMALLINT = 2010

SELECT 1 + Number AS WeekNumber,
StartDate,
CASE Number
WHEN 52 THEN DATEADD(YEAR, @theYear - 1899, -1)
ELSE DATEADD(DAY, 6, StartDate)
END AS EndDate
FROM (
SELECT DATEADD(DAY, 7 * Number, DATEADD(YEAR, @theYear - 1900, 0)) AS StartDate,
Number
FROM master..spt_values
WHERE Type = 'P'
AND Number < 53
) AS w[/code]

N 56°04'39.26"
E 12°55'05.63"

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 08:41:57

[code]DECLARE @Year INT SET @Year = 2010
DECLARE @First DATETIME

SET @First = CAST(@Year AS VARCHAR) + '0101'

SELECT WeekNo, StartDate, DATEADD(d, 6, StartDate) AS EndDate
FROM (
SELECT number + 1 AS WeekNo, DATEADD(wk, Number, @First) AS StartDate
FROM master..spt_values
WHERE Type = 'P'
AND Number < 53) z[/code]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:06:38

quote:

Originally posted by Peso

DECLARE	@theYear SMALLINT = 2010

SELECT	1 + Number AS WeekNumber,
	StartDate,
	CASE Number
		WHEN 52 THEN DATEADD(YEAR, @theYear - 1899, -1)
		ELSE DATEADD(DAY, 6, StartDate)
	END AS EndDate
FROM	(
		SELECT	DATEADD(DAY, 7 * Number, DATEADD(YEAR, @theYear - 1900, 0)) AS StartDate,
			Number
		FROM	master..spt_values
		WHERE	Type = 'P'
			AND Number < 53
	) AS w

N 56°04'39.26"
E 12°55'05.63"

This is posted in 2005 forum

so

DECLARE @theYear SMALLINT = 2010

should be

DECLARE @theYear SMALLINT
set @theYear =2010

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:14:36

To DBA in the making,

When you cast or convert to character datatypes, always specify the length.

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 09:56:06

quote:
Originally posted by madhivanan

To DBA in the making,

When you cast or convert to character datatypes, always specify the length.

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Not really needed when casting/converting an INT to a VARCHAR, as the default for a VARCHAR is 30 characters, and an INT will never be that long (esp. if it represents a valid year).

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:57:30

quote:
Originally posted by DBA in the making

quote:
Originally posted by madhivanan

To DBA in the making,

When you cast or convert to character datatypes, always specify the length.

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Not really needed when casting/converting an INT to a VARCHAR, as the default for a VARCHAR is 30 characters, and an INT will never be that long (esp. if it represents a valid year).

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Yes. I said it as a general practice.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources