Obtaining year and week numbers from a date in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Obtaining year and week numbers from a date...

Obtaining year and week numbers from a date in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

asbains8

Posted Wednesday, May 14, 2008 9:57 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:51 AM
Points: 16, Visits: 254

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.

Post #500664

RyanRandall

Posted Wednesday, May 14, 2008 10:06 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.

Post #500685

RyanRandall

Posted Wednesday, May 14, 2008 10:14 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.

Post #500699

antonio.collins

Posted Wednesday, May 14, 2008 12:51 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437, Visits: 883

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

Post #500839

asbains8

Posted Wednesday, May 14, 2008 3:37 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:51 AM
Points: 16, Visits: 254

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

Post #500932

Jeff Moden

Posted Wednesday, May 14, 2008 10:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 32,930, Visits: 26,818

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #500994

« Prev Topic | Next Topic »

Permissions