Week Number in SQL Server 2005

Last Post 10 Apr 2007 09:17 PM by SwePeso. 55 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 1 of 3123 > >>
Author Messages
nailers67
New Member
New Member

--
30 Mar 2007 06:20 AM
I have been trying to get SQL Server to return week 1 for December 31, 2006. We treat the weeks of the year to always start on the Sunday of the week that January 1 falls on. Is there an easy way to do this? Can someone help me out.

Thanks in advance.
nosepicker
New Member
New Member

--
30 Mar 2007 09:04 AM
This is kind of messy, but I think it works - maybe someone else can come up with a more succinct solution:

DECLARE @date datetime
SET @date = '20061231'

SELECT CASE WHEN @date NOT BETWEEN DATEADD(day, -DATEPART(dw, (DATEADD(year, DATEDIFF(year, 0, @date) +1, 0))), (DATEADD(year, DATEDIFF(year, 0, @date) +1, 0))) +1 AND DATEADD(year, DATEDIFF(year, 0, @date) +1, 0) -1 THEN DATENAME(week, @date)
ELSE 1 END

nailers67
New Member
New Member

--
30 Mar 2007 02:07 PM
Thank you for the solution. This will work for this instance.

I guess there is a further detailed question as to how one could get SQL Server to treat any day of December that falls in the same week as January 1 as week 1 of the following year. I don't know if this is even possible, but in Outlook's calendar, it treats the specific day in December as the first week.

Thanks again.....appreciate it.
nosepicker
New Member
New Member

--
30 Mar 2007 02:17 PM
I wrote that query to handle all possible days in December that might be in the same week as January 1. Try it out.
SQLUSA
New Member
New Member

--
30 Mar 2007 03:56 PM
nailers67

The split week at the end of the year is very messy!

Difficult to handle. While months, quarters neatly fit in, not weeks.

If you find an elegant way of handling it, let us know.

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com
nailers67
New Member
New Member

--
30 Mar 2007 04:12 PM
i just ran it and it appears to do exactly what you say.....this works well for what i need. EXCELLENT JOB. Much appreciated.

Thanks for all your hard work.
SwePeso
New Member
New Member

--
01 Apr 2007 06:20 AM
NP's algorithm is beautiful, but I have found DATEPART function to be erratical about WEEKDAY option. Not that SQL Server calculates the wrong day, but because it is dependant of SET DATEFIRST setting.

This method do not rely on DATE DATEFIRST setting

DECLARE @date datetime
SET @date = '20061231'

SELECT CASE
WHEN @date >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, -1, @date), 0)) / 7 * 7, -1) THEN 1
ELSE DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0)) / 7 * 7, -1), @date) / 7 + 1
END


And yes, SQLUSA, you are allowed to copy this code and use on your site.
nailers67
New Member
New Member

--
01 Apr 2007 02:53 PM
Thank you also for your recommendation. I am reading it also to try and follow what is happening so I understand it. Another quick question for you if you dont mind, how could you start the week on Monday - Sunday using the same query instead of Sunday - Saturday?

There is word we may start reporting our starting of the week for Monday instead of Sunday, what it is today.

Also, is the "year" for what week that date falls on able to be extracted? For example, if December 31, 2008 falls on a Tuesday, is there a way to put the year as 2009 since it is really week 1 of 2009?


Thanks again, I appreciate all your help.

SwePeso
New Member
New Member

--
02 Apr 2007 01:51 AM
Replace the date {-1} with {0}.
SQL Server treats date 0 as Jan1, 1900 which incidentally is a monday. Date -1 is a sunday.

The best thing you could do is to make some kind of permanent calendar table.
Michael Valentine Jones has an excellent function here

http://www.sqlteam.com/forums/topic...C_ID=61519
nailers67
New Member
New Member

--
02 Apr 2007 04:47 AM
Peter, thanks for the time, help and suggestions. I will work with them today.

Regards
Bob
SQLUSA
New Member
New Member

--
05 Apr 2007 04:33 PM
Peter,

You can submit your script to be posted under your name: http://www.sqlusa.com/bestpractices/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices/ .

Just click on Support .

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com
nosepicker
New Member
New Member

--
06 Apr 2007 07:41 AM
This is a typical sqlusa thread. Everyone else does all the work, and then he tries to swoop in and get the glory, without actually saying anything meaningful. And he gets his post count up to try to fool newbies into thinking that he actually knows what he's talking about, not to mention the callous abuse of unsolicited advertising. Really pathetic.
SQLUSA
New Member
New Member

--
07 Apr 2007 02:05 AM
Nailers67,

Check out if this elegant solution works for you:

DECLARE @date datetime
SET @date = '20061231'


SELECT CASE
WHEN DATENAME(week, @date) = 53
AND DATEPART(weekday, @date) != 7
THEN 1
ELSE
DATENAME(week, @date)
END

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com
SwePeso
New Member
New Member

--
07 Apr 2007 04:09 AM
Still dependant of SET DATEFIRST setting..
If you are going to post a better/neater solution, please make sure it works!


set datefirst 1 -- tell sql server to treat monday as first day of week (violating business rule?)

DECLARE @date datetime
SET @date = '20061231'


SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 7 THEN 1
ELSE DATENAME(week, @date)
END

------------------------
set datefirst 7 -- tell sql server to treat sunday as first day of week (violating business rule?)

SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 7 THEN 1
ELSE DATENAME(week, @date)
END
SQLUSA
New Member
New Member

--
07 Apr 2007 06:25 AM
Good try Peter, but no cigar. Actually my script works for default settings. For your version try this:

set datefirst 1 -- tell sql server to treat monday as first day of week (violating business rule?)

DECLARE @date datetime
SET @date = '20061231'


SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 6 THEN 1
ELSE DATENAME(week, @date)
END
GO
------------------------

DECLARE @date datetime
SET @date = '20061231'

set datefirst 7 -- tell sql server to treat sunday as first day of week (violating business rule?)

SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 7 THEN 1
ELSE DATENAME(week, @date)
END



Kalman Toth, Business Intelligence Architect
SQL Server 2005 Training - http://www.sqlusa.com/businessintelligence
SwePeso
New Member
New Member

--
07 Apr 2007 12:26 PM
Yes? I did try this

set datefirst 1 -- tell sql server to treat monday as first day of week (violating business rule?)

DECLARE @date datetime
SET @date = '20061231'

SELECT CASE
WHEN @date >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, -1, @date), 0)) / 7 * 7, -1) THEN 1
ELSE DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0)) / 7 * 7, -1), @date) / 7 + 1
END
GO
------------------------

DECLARE @date datetime
SET @date = '20061231'

set datefirst 7 -- tell sql server to treat sunday as first day of week (violating business rule?)

SELECT CASE
WHEN @date >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, -1, @date), 0)) / 7 * 7, -1) THEN 1
ELSE DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), 0)) / 7 * 7, -1), @date) / 7 + 1
END


And I got Week 1 with both queries.
What was your point?
SwePeso
New Member
New Member

--
07 Apr 2007 12:32 PM
And Kalman, please change the test date 20061231 to 20061230 and see what your two queries return!
They are inconsistent since they will return 53 and 52.

My queries will return 52 and 52 with the test date 20061230.

If you are going to critizie, please be right!

//Peter Larsson
SwePeso
New Member
New Member

--
07 Apr 2007 12:44 PM
quote:

Originally posted by: SQLUSA
Good try Peter, but no cigar. Actually my script works for default settings. For your version try this:

set datefirst 1 -- tell sql server to treat monday as first day of week (violating business rule?)

DECLARE @date datetime
SET @date = '20061231'

SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 6 THEN 1
ELSE DATENAME(week, @date)
END
GO
------------------------
DECLARE @date datetime
SET @date = '20061231'

set datefirst 7 -- tell sql server to treat sunday as first day of week (violating business rule?)

SELECT CASE
WHEN DATENAME(week, @date) = 53 AND DATEPART(weekday, @date) <> 7 THEN 1
ELSE DATENAME(week, @date)
END

Kalman Toth, Business Intelligence Architect


AAAAAAAAAAAAAAHHHHHHHHHH!!!!!!!!!!!! Now I see what you have done....
You have changed the <> 7 to <> 6!.
That means you have to manually go and change the number according to which year it is!

Don't you think that will be hard to do, if the @date for example is a parameter to a stored procedure where this code/algorithm reside?

I feel the urge to ask you one thing;

"Architect of what?"...

If I followed your advice and put them into production to my customers, my reputation would vanish and I might even go bancrupt it I followed your advice here!

For the rest of you, if I were in your seats, I would be worry for my professional position if I followed this man's advice. That's my personal opinion, you have to make your own.

BUT... What would you think happens when you have to alter the code for each new year to input to the stored procedure? Do you think your managers will approve?
nosepicker
New Member
New Member

--
07 Apr 2007 05:29 PM
Yup, welcome to our world, Peter. This is the kind of person we have to put up with on this forum. SQLUSA's behavior and incompetence certainly wouldn't tolerated by sqlteam, eh? He would merit his own separate "twit list", don't you think?
SwePeso
New Member
New Member

--
07 Apr 2007 11:19 PM
I started with this forum in may 2006 and in the beginning I too was dazzled by SQLUSA.
But it took me only 40-50 posts to answer to realize what SQLUSA were up to.

I don't like that behaviour so I went on to SQLTeam.
You are not authorized to post a reply.
Page 1 of 3123 > >>


Acceptable Use Policy