quote:

---

Originally posted by druer
Why do accountants get to make such complicated/silly rules?...

---

quote:

---

Originally posted by druer

Why do accountants get to make such complicated/silly rules? If you asked a first grader what January 1, 2006 was they would tell you that it was the first day of the year. It takes a college degree, MBA and passing a test for someone to be able to suggest that January 1, 2006 is in a week for 2005. :)

I bought two cans of beans. (Leased 2 cans of beans I should never buy them right.) How would they respond if I said I asked them which can of beans does the first bean belong to that I pull out of the second can? Would they say you need to count how many beans were in the first can first to see if you were a bean short?

Here is my thought process, some weeks (like Week 52 in 2005) may be a few days short. January 1, 2006 belongs to the year 2006 and can't be "borrowed" to make up for a short week in 2005. Those accountants have screwed up enough other things, please keep them away from my SQL Server. In SQL us "logical" minded folks get to make the rules. :)

---

SELECT    DATEPART(WW,'1/1/2005')
	, DATEPART(WW,'1/2/2005')
	, DATEPART(WW,'1/7/2005')
	, DATEPART(WW,'1/8/2005')
	, DATEPART(WW,'12/30/2005')
	, DATEPART(WW,'12/31/2005')

quote:

---

Originally posted by X002548
Actually, it looks like 1/1/05 belongs to week 52 of 2004.

---

-- Script to find first day of week for an ISO year and week

declare	@year	int
declare	@week	int

-- Pick a year and week
select @year	= 2005
select @week	= 52

select
	StartOfYearWeek =
	-- If it is in the year passed,
	-- return start of week,
	-- otherwise, return null.
	case
	when @week < 1
	then null
	when dateadd(dd,(@week-1)*7,CurrYrStart) < NextYrStart
	then dateadd(dd,(@week-1)*7,CurrYrStart)
	else null
	end
from
(
select
	-- First day of first week of current year
	CurrYrStart =
	dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
	-- First day of first week of next year
	NextYrStart =
	dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
	--Find Jan 4 for input year
	Jan4	= 
	case
	when @year between 1753 and 9998
	then dateadd(dd,3,dateadd(yy,@year-1900,0))
	-- Null if not in valid year range
	else null end
) aa
) a

quote:

---

Originally posted by jsmith8858

I think that, by far, the easiest way to do this is to create a permanent table of Weeks in your database,...

---

quote:

---

Originally posted by Michael Valentine Jones

quote:

---

Originally posted by jsmith8858

I think that, by far, the easiest way to do this is to create a permanent table of Weeks in your database,...

---

I agree with that, although our table is for individual dates. I actually pulled the logic for the UDF from the stored procedure I use to load our table.

CODO ERGO SUM

---