Obtaining year and week numbers from a date in SQL

Question

Post reply

Obtaining year and week numbers from a date in SQL

asbains8

Say Hey Kid

Points: 690
More actions
May 14, 2008 at 9:57 am

#182431

Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week number together for a date i.e. the 01/01/2008 would be 200801. I know how to get them seperately using datepart but i need them joined together.
Any help would be much appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

select ltrim(year('01/01/2008')) + right('0' + ltrim(datepart(week, '01/01/2008')), 2)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

If you want a whole bunch of them, you should consider making use of this date function...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

select DATE, ISO_YEAR_WEEK_NO from dbo.F_TABLE_DATE('1/1/2008', '1/1/2009')

Better still, use that function to create a permanent, fully-indexed table, and then use that.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

select year(getdate()) * 100 + datepart(week,getdate()) as year_week

asbains8 Say Hey Kid Points: 690 More actions · Answer 4

Thanks guys i will try your suggestions and let you know the outcome.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Ack... double post... :sick:

http://www.sqlservercentral.com/Forums/Topic500305-5-1.aspx?Update=1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)