Week Number in SQL Server 2005

Last Post 10 Apr 2007 08: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 2 of 3 << < 123 > >>
Author Messages
SQLUSA
New Member
New Member

--
08 Apr 2007 12:58 AM
Forget Peter!

This is my original post:

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

It works for the default setting of SQL Server 2005. You don't have change it from year to year.

As far as your script concerned, I don't know what are you trying to accomplish by overcomplecating.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlsua.com
SQLUSA
New Member
New Member

--
08 Apr 2007 01:02 AM
Nosejob,

Just stick to SQL and leave behind philosophy, you are not a philosopher.

As far as SQL concerned, you have much too improve. You are a good SQL programmer, but you like hard-wiring scripts.
SQLUSA
New Member
New Member

--
08 Apr 2007 01:06 AM
Russell,

I grew up in Communist Hungary. The Communist state encouraged neighbors reporting on neighbors anonymously. Similarly, anonymous alias usage on this forum brings out the worst from some people. I am ashamed of you.

Using alias instead of your real name brings out the worst behaviour from you too.

Just stick to SQL and use your own legal name.
SQLUSA
New Member
New Member

--
08 Apr 2007 01:23 AM
Peter,

Why and when you would use SET DATEFIRST ?

Is there a downside to it if you set it differently from default?

Actually I found it strange that the week starts with Sunday by SQL Server default, I would think of Monday as start of the week.

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

--
08 Apr 2007 04:06 AM
quote:

Originally posted by: SQLUSA
Forget Peter!
...
It works for the default setting of SQL Server 2005. You don't have change it from year to year.

As far as your script concerned, I don't know what are you trying to accomplish by overcomplecating.


But your script does not work for test date 20001231!
Not even with the default setting of SET DATEFIRST 7...

Your code returns the week of 54! I have never heard of that week number before in my whole life, and I did my thesis on date calculations in high school.

Do you think it is good practice to provide suggestions that are not complete nor accurate?
Why would you suggest an algorithm that returns week number 54 for a perfectly valid test date 20001231?
Who, or which analyst, in the whole wide world would accept that week number?

And for these dates in modern time, your code also returns with week number 54; 19161231, 19441231, 19721231, 20281231, 20561231 and 20841231.

And for these 210 dates, I have found your algorithm to calculate the wrong week number according to original posting!


Time period Kalman Peso
-------------------- ------ ----
19041225 to 19041230 1 53
19101225 to 19101230 1 53
19161224 to 19161229 1 53
19211225 to 19211230 1 53
19271225 to 19271230 1 53
19321225 to 19321230 1 53
19381225 to 19381230 1 53
19441224 to 19441229 1 53
19491225 to 19491230 1 53
19551225 to 19551230 1 53
19601225 to 19601230 1 53
19661225 to 19661230 1 53
19721224 to 19721229 1 53
19771225 to 19771230 1 53
19831225 to 19831230 1 53
19881225 to 19881230 1 53
19941225 to 19941230 1 53
20001224 to 20001229 1 53
20051225 to 20051230 1 53
20111225 to 20111230 1 53
20161225 to 20161230 1 53
20221225 to 20221230 1 53
20281224 to 20281229 1 53
20331225 to 20331230 1 53
20391225 to 20391230 1 53
20441225 to 20441230 1 53
20501225 to 20501230 1 53
20561224 to 20561229 1 53
20611225 to 20611230 1 53
20671225 to 20671230 1 53
20721225 to 20721230 1 53
20781225 to 20781230 1 53
20841224 to 20841229 1 53
20891225 to 20891230 1 53
20951225 to 20951230 1 53


If you want me to, I can post a few thousand more dates where your code calculates the wrong week.



I hope people in the future look at this thread (maybe even some other link to this thread to beware of you) to see what kind of an imbecill you are.
You give wrong and inaccurate responses. I hope people who has bought your "education package" call you to get their money back! Really...

How can you tell this is "best practices"? If people follow your advice, what kind of future will they have?
SwePeso
New Member
New Member

--
08 Apr 2007 04:38 AM
I have looked at some of your "best practices" and found errors (or inefficiency) in at least 50% of them.
It seems to me you have taken some knowledge from SQL Server 2000 and translated them into SQL Server 2005, without using the new SQL Server 2005 functions and command to the full.

If you want me to, I can post the erratical codes (or codes that can be made more efficient) here?

I can start with

How to generate a date sequence ( why update later? )
How to find the second highest salary ( why not use MIN? )
How to create a function to count characters in a string ( Ever heard of trailing spaces? )
How to calculate the median ( Heard of TOP 50 PERCENT? )
How to generate a sequence ( People don't use the number range from -50 to 50? )
How to count the days in a month excluding Sundays ( Speed anyone? )
SwePeso
New Member
New Member

--
08 Apr 2007 10:27 AM
SET DATEFIRST 7

SELECT theDate,
DATENAME(WEEKDAY, theDate) AS WeekdayName,
CASE
WHEN DATENAME(WEEK, theDate) = 53 AND DATEPART(WEEKDAY, theDate) <> 7 THEN 1
ELSE DATENAME(WEEK, theDate)
END AS Kalman,
CASE
WHEN theDate >= DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, -1, theDate), 0)) / 7 * 7, -1) THEN 1
ELSE DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, theDate), 0)) / 7 * 7, -1), theDate) / 7 + 1
END AS Peso
FROM (
SELECT CAST('20001222' AS DATETIME) AS theDate UNION ALL
SELECT '20001223' UNION ALL
SELECT '20001224' UNION ALL
SELECT '20001225' UNION ALL
SELECT '20001226' UNION ALL
SELECT '20001227' UNION ALL
SELECT '20001228' UNION ALL
SELECT '20001229' UNION ALL
SELECT '20001230' UNION ALL
SELECT '20001231' UNION ALL
SELECT '20010101' UNION ALL
SELECT '20010102' UNION ALL
SELECT '20010103' UNION ALL
SELECT '20010104' UNION ALL
SELECT '20010105' UNION ALL
SELECT '20010106' UNION ALL
SELECT '20010107' UNION ALL
SELECT '20010108'
) AS d
ORDER BY theDate
nosepicker
New Member
New Member

--
08 Apr 2007 02:16 PM
I also completely agree with Russell's deftly written post. Kalman, to summarize what Russell wrote, it comes down to this: motive. What are your motives for being here? 99.999% of us are here without the thought of financial gain. In fact, the ones who post answers frequently actually do so at the expense of financial gain, since we often taken time out of our work schedules to answer questions. You, on the other hand, take advantage of this forum's openness, prey on the naivete of the inexperienced, and ultimately trample on the fabric that all public forums were built upon - all of this just to make a dollar. You attempt to tear down what we all work so hard to build up. In the world of discussion forums, you are the anti-Christ.
SQLUSA
New Member
New Member

--
08 Apr 2007 02:50 PM
Listen nosejob!

You are not a trainer, you don't understand training.

If someone posts a question, you give the anwer. I am stating again, you are a good SQL programmer, but you need improvement, you have to get away from hard-wiring. You have to more object-oriented.

I prefer giving newbie-s direction to the solution. That is helpful, that is constructive.

I am learning from you, and I do hope you are learning from me.

Boys & girls, enough chat! BACK TO SQL!!!!
SQLUSA
New Member
New Member

--
08 Apr 2007 03:06 PM
Russell,

It is the anonymous postings which brings out the worst of some people in this forum.

Look at rm. He is an extremely talented DBA. Why isn't he using his legal name? Isn't that suspicous to you?

Nosejob. He is a good SQL developer. He got very upset with me because I dared to criticize his coding style.

I participate in other forums, there are some heated debates there too, but people are generally using their names and more or less civilized.

I am not violating any rules contrary to beliefs. I am using my signature blocks officially on all forums.

I am learning from you, and hopefully you are learning from me.

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

--
08 Apr 2007 08:37 PM
HAHAHAHAHA! Upset that you criticized my coding style? Please. That's like getting upset because a 3-year old child criticized my coding style. You don't know enough about SQL coding to even begin to formulate a thought about constructively criticizing SQL coding. You obviously don't even know what "hard-wiring" is.

You prefer giving newbies "directions" to the solution because you don't know enough to give a specific answer. That was proven with your so-called "elegant" solution in this thread. This was the first time I can remember you trying to give a specific answer, and of course it was completely wrong. Only someone with no real-life experience with SQL coding gives vague generalities instead of specific answers to questions.

We already had a discussion about the right to use screennames and pseudonyms on forums. In this age of identity theft and information piracy, some of us prefer not to use our real names simply for our own protection. And since we're not interested in selling anything or making a profit, who cares what name is used? We let our posts speak for themselves.

We are definitely learning from you. We are learning how little knowledge you actually possess and we are learning how are you are still trying to turn that into a profit. We learn this about you with every post you write. Just like you said, yes, please, let's get back to SQL. The rest of us will, which is what we've been doing all this time, instead of selling, which is what you've been doing.
SwePeso
New Member
New Member

--
08 Apr 2007 10:19 PM
1) Why don't SQLUSA criticize me? I am the one who put him down this time...
2) Why don't SQLUSA admit his suggestion in this thread has flaws? I have proved him wrong several times...

Oh... Wait! I think SQLUSA wrote "Forget Peter" some posts ago. I wonder what he meant with that?
SwePeso
New Member
New Member

--
08 Apr 2007 10:23 PM
quote:

Originally posted by: SQLUSA
I prefer giving newbie-s direction to the solution. That is helpful, that is constructive.

That is not what you have answered me in this forum, when I have given "the direction" to the solution...
Such as this topic http://sqlforums.windowsitpro.com/w...erthread=y

skt5000
New Member
New Member

--
09 Apr 2007 03:56 AM
From a "Newbie"
Although I am not a "Newbie", I am far away from being a good programmer. I am reminded of this every time I spend many futile hours trying to figure something out, giving up and posting the problem here, only to have one of the people on this thread solve the problem elegantly and efficiently and usually within minutes of posting. I appreciate getting the answer to my questions so I can get back to work. It would be great if the answers contained some type of explanation as to why they work, or where my thinking was off the rails, but I am grateful for the answer and realize that everyone has their own work to go back to. I don't mind Kalman's responses and I don't question his motives. It takes longer to do that than it does for me to realize that his responses don't help me at all. Since his responses aren't helpful I don't bother going to his web site and so am in no danger of being misled. I am sure that there are many others like me. I wish the time spent refuting Kalman's competence, which is best done by letting people like me read his responses, were spent adding into why something works. But given the choice between the fish and learning how to fish, I can't concentrate when I'm hungry, so will take the fish every time

Jim
nosepicker
New Member
New Member

--
09 Apr 2007 06:59 AM
Jim, I wholeheartedly agree with your sentiment. I'm glad you have had the instincts to ignore Kalman's unhelpful responses. What I and other contributors to this forum worry about is the potential for people less experienced than you to be easily misled by misinformation. The fact that Kalman takes that misinformation and tries to parlay it into a profit is even more galling. I can't necessarily speak for others, but I know that if someone asks for a more detailed explanation of an answer I've given, I'm more than happy to provide it. All they have to do is ask. I guess people here don't always give detailed explanations because of either lack of time or because it might seem like overkill.
sanjayattray
New Member
New Member

--
09 Apr 2007 12:25 PM
what a thread. It took me 1/2 hr to go through it. My jobs went good this week end, so I'm enjoying Monday. But, being part of this forum for quite some time, you get to know to whose solution you should consider as valid/good and whose are not. You cannot perform solutions provided by people here directly on prod. servers. There are definitely few knowledgeable people in this forum who had bailed me out lot of times. for others reply like............ I just ignore them.
SQLUSA
New Member
New Member

--
09 Apr 2007 04:18 PM
Peter: 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.

=============================================
What brought you back?

Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/

SQLUSA
New Member
New Member

--
09 Apr 2007 04:21 PM
Peter,

I really appreciate your feedback. I will take a look at those HOW TO-s....


Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/
SQLUSA
New Member
New Member

--
09 Apr 2007 04:27 PM
Nosejob,

What is your real name? Why are you hiding behind such a lousy alias. You are an ok developer, no reason to hide.

Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/
SQLUSA
New Member
New Member

--
09 Apr 2007 04:29 PM
Obviously, if you don't want to learn how to fish, just mark the subject as URGENT! Then we know you just want the fish.

Kalman Toth, Database, Data Warehouse & Business Intelligence Architect
SQL Server Training - http://www.sqlusa.com/ordertriplecrown/
You are not authorized to post a reply.
Page 2 of 3 << < 123 > >>


Acceptable Use Policy