getting week number of the current year

Question

getting week number of the current year

Viewing 5 posts - 16 through 19 (of 19 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ALZDBA (9/19/2008)
There is this very nice and eye-opening article concering ranges ...
Called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
qa.sqlservercentral.com/articles/TSQL/62867/

Nobody listens on a friday 🙁

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

I'm used to that :w00t:

1) married

2) dba

:D:):P:w00t::hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

neil.b.roberts Newbie Points: 3 More actions · Answer 3

It sounds like you are after more of a calendar style output

[Code]

declare @yr as nvarchar(4)

declare @yrpre as nvarchar(4)

declare @wk as nvarchar(2)

declare @mt as varchar(2)

declare @lowerBound as datetime

declare @upperBound as datetime

set @wk = 52 -- Week

set @yr = '2008' -- Year

set @yrpre = @yr - 1

set @mt = (select cast( datepart( mm, dateadd(dd, (@wk * 7), cast(( @yrpre + '-12-31') as datetime ) )) as varchar(2)))

set @lowerBound = (select dateadd(mm,-1, cast(@yr + '-' + @mt + '-1' as datetime)))

set @upperBound = (select dateadd(mm,1,cast(@yr + '-' + @mt + '-1' as datetime)))

print @lowerBound

print @upperBound;

with cteCal(dyWeek, yrWeek) as

(

select @lowerBound , datepart( wk, @lowerBound + 1 )

union all

select dyWeek + 1, datepart( wk, dyWeek + 1 )

from cteCal

where dyWeek + 1 < @upperBound

)

select dyWeek [day date]

,datepart(dy, dyWeek) [day of year]

,datename(dw, dyWeek) [day]

,datepart(dw, dyWeek-1) [day of week]

,datepart(dd, dyWeek) [day of month]

,datepart(ww, dyWeek) [week]

,datepart(mm, dyWeek) [month]

,datename(mm, dyWeek) [month]

,datepart(qq, dyWeek) [quarter]

,datepart(yy, dyWeek) [year]

from cteCal

where

yrWeek = @wk

OPTION (MAXRECURSION 0)

[/Code]

you would want to make the bounding conditions better.

There are quite a few good examples on the net

http://www.databasejournal.com/features/mssql/article.php/3502256

http://blogs.conchango.com/jamespipe/archive/2007/04/24/T_2D00_SQL-Calendar-table.aspx

etc

you may also want to look into Tally tables

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

ALZDBA (9/19/2008)
I'm used to that :w00t:
1) married
2) dba
:D:):P:w00t::hehe:

And if you have children you will lose your voice completely 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

Just extracting the week number for the year from the date can be ambiguous depending on your business needs.

For example I wrote the back end for a major movie box office reporting system. In this case, since box office weekend numbers are the most important, a week was based on Saturday. The last Saturday in December was the last week number for the year. The first Saturday of the year was in week number 1 for that year.

The best way to handle this sort of thing is to create a date table defining your weeks and index the Start/End dates for the week. That way you never have to calculate anything and you can use the indexes in the date table to optimize performance. Our table looked something like this:

CREATE TABLE JulWeek

( JulWeek INT IDENTITY(1, 1) -- Primay Key

, YearNum INT

, WeekNum INT -- This goes from 1 to 52 or 53 for the year

, WeekStart DATETIME

, WeekEnd DATETIME

)

To get the week number just use something like:

SELECT YearNum, WeekNum FROM JulWeek

WHERE MyDate BETWEEN WeekStart And WeekEnd

Todd Fifield