week number in sql server - 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

week number in sql server

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-12 : 15:14:30

For the following query, I'm getting dates from 1st Jan to 4th Jan. But I want it to be from 30th December, 2013 to Jan 5th, 2014 as per week number. How can I do that?


select date, sum(qty1), sum(qty2) from table1
where datepart(wk,date) = 1
group by date

For your information, I'm using sql server 2005.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-12 : 15:24:27

try isowk instead of wk, Here's a function that will do that in ss2005:

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/iso-week-in-sql-server/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-12 : 15:39:37

Or much much shorter and faster
http://www.sqltopia.com/?page_id=89

SELECT	(DATEPART(DAYOFYEAR, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7 + 3) + 6) / 7

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources