What period of the year? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

What period of the year?

Author

Topic

GunZ
Starting Member

29 Posts

Posted - 2005-02-10 : 18:48:25

The function

datepart(wk,'2004-11-17')

will return the value 47, telling that the day belongs to week number 47 for the year 2004.

The function

datepart(wk,'2001-11-17')

will return the value 46, telling that the day belongs to week number 46 for the year 2001.

The function

datepart(wk,'1991-11-17')

will return the value 47, telling that the day belongs to week number 46 for the year 1991.

The function

datepart(wk,'1990-11-17')

will return the value 46, telling that the day belongs to week number 46 for the year 1990.

Now, given a week number between 1 and 52 (inclusive) for any particular year, how do you get the period lengths, and dates belong to that period (i.e. what day/date does the period begin/end)?

Australia.NSW.Sydney.GunZ

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-10 : 22:02:37

First of all, with the default @@DateFirst setting these are the periods for second and last day of 2005:
(might not be what you expect)


select	datepart(week, '1/2/2005') [1/2/2005]
	,datepart(week, '12/31/2005') [12/31/2005]

--Here is one possible solution:

--This solution needs a table of numbers starting at 0 for every day of the year
set nocount on
declare @numbers Table (DayOfYear int NOT NULL Primary Key nonclustered)
declare @i int
Select @i = 0
while @i <= 365 --366 days (0-365) - where clause will handle non-leap year years
begin
	insert @numbers values(@i)
	Set @i = @i+1
End

--Change the @week and @year variable values to test
declare @week int
	,@year char(4)

Select @week = 1
	,@year = 2000


Select	@week [week]
	,@year [year]
	,min([date]) PeriodStart
	,max([date])PeriodEnd
From	(
	Select	dateAdd(day, DayOfYear, '1/1/' + @year) [date]
	From	(Select @week [week]) as wk
	cross join @numbers n
	Where	datepart(week, dateAdd(day, DayOfYear, '1/1/' + @year)) = @week
	AND	364 >= case when convert(int,@year) % 4 = 0 then 364 Else DayOfYear End
	) as dates

Be One with the Optimizer
TG

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-02-11 : 20:18:25

You can also use these functions to find the beginning and ending dates of a particular week of a year (this assumes that the week starts on Sunday and ends on Saturday):

CREATE FUNCTION week_start (@year AS int, @week AS int)
RETURNS datetime
BEGIN
DECLARE
@week_start datetime

SELECT @week_start = DATEADD(wk, DATEDIFF(wk, 6, DATEADD(wk, @week -1, CAST(@year AS char(4)) + '0101')), 6)

RETURN @week_start
END

CREATE FUNCTION week_end (@year AS int, @week AS int)
RETURNS datetime
BEGIN
DECLARE
@week_end datetime

SELECT @week_end = DATEADD(wk, DATEDIFF(wk, 5, DATEADD(wk, @week -1, CAST(@year AS char(4)) + '0101')), 5)

RETURN @week_end
END

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-11 : 21:01:42

>> Now, given a week number between 1 and 52 (inclusive) for any particular year,
What about week 53?
jan 1 is always week 1. Week 2 starts depending on datefirst. There will always be a week 53 and I guess possibly a week 54.
I think the week ending function above will have problems with the last week in the year as this should end on dec 31.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-02-12 : 00:40:29

That's a good point. I suppose something like the following statement should be added to the "week_end" function:

IF YEAR(@week_end) > @year
SET @week_end = CONVERT(datetime, CAST(@year AS char(4)) + '1231')

GunZ
Starting Member

29 Posts

Posted - 2005-02-13 : 03:44:40

Hmm... split weeks... 54 weeks in a year... haven't thought of that... but I'll give nosepicker's function a go. It has no db foot print :D

Australia.NSW.Sydney.GunZ

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-13 : 09:13:17

GunZ,
if you change your mind for some reason, I will edit my earlier solution to deal with leap years so that periodStart and periodEnd are always in the specified year.

But since you're going with nosepicker's solution just remember somthing my mother used to tell me,
"Use a tissue, not your finger"

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources