Week Number - 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 2005 Forums

Transact-SQL (2005)

Week Number

Author

Topic

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 04:07:57

Good Morning
I have pondered over this for the last hour and every thing I have tried does not work.
I currently run an update that insert's todays date and a few other things I am trying to get the week number, now the code I am using works great it will get me the current week number but my reporting week starts Thursday so I should be on week 29 not 28.
My Code that I use is

Update dbo.DateConfigTest
SET WeekNo = DATENAME(wk, dbo.DateConfigTest.Date)
WHERE WeekNo IS NULL

Any help would be great.

Many thanks

Whitmoj
If I have inspired one person today then my job is done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 04:14:37

What constitutes your definition of a week?

N 56°04'39.26"
E 12°55'05.63"

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 04:16:08

Our week runs from a thursday to a wednesday inc

Whitmoj
If I have inspired one person today then my job is done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 04:20:08

What about the first week of year?
is that the week of first FULL thu-wed?
Is that the week of Jan 1st within thu-wed?

N 56°04'39.26"
E 12°55'05.63"

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 04:23:51

It is the week of first Full thu-wed

Whitmoj
If I have inspired one person today then my job is done.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 04:29:00

From BOL:

quote:

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

So this years weeks start on a Thursday anyway. We are in week 28. So if you were to do:


set datefirst 4;

select @@datefirst, datepart(wk,getdate()-2)

You would get week 27, but if you do:


set datefirst 4;

select @@datefirst, datepart(wk,getdate()+6)

You get week 29.

Try it with different dates and you'll see.

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 04:34:00

Thats fine but I have previous data to change (min this year so far) and I have to do this 100 times in a SP. So if I can apply something to my code already this would be great.

Whitmoj
If I have inspired one person today then my job is done.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 04:37:50

so set datefirst 4 at the top of the procedure. I don't see this as much of a problem as it stays whatever you set it for the length of the session.

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 04:42:46

This code does not take the date from the date column that is already populated by the date. It will work going forward but not for previous data (select @@datefirst, datepart(wk,getdate()+6))

Whitmoj
If I have inspired one person today then my job is done.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 04:51:09

What do you mean? Perhaps if you posted the code you are trying to change, you would get the answer you are after. If you need to update data you already populated incorrectly, a simple update would make the desired changes.

If it is just your initial query, then:


set datefirst 4;

Update dbo.DateConfigTest
SET WeekNo = DATENAME(wk, dbo.DateConfigTest.Date)
WHERE WeekNo IS NULL

will work for those that are null. For those that aren't you will need to just change the update, perhaps change the where clause to something like WHERE datepart(yyyy,dbo.DateConfigTest.Date) = 2009. Calling your Date field Date is really bad practice BTW.

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 05:05:32

I have inherited the table and as there are 9 other SP's that connect to it that we use and approx another 50 else were in the world. Changing the name will be a task and half. I have data going back to 2001 in this table and the powers that be have changed the start day of the week. The only reason I wish to back date the week number is for our web front end week display.

Whitmoj
If I have inspired one person today then my job is done.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-10 : 05:11:13

Do you need to change all the data in the table back to 2001? If so, just lose the where clause and it will work. To test it just run it as a select first:

select DATENAME(wk, dbo.DateConfigTest.Date) as weekno
from dbo.DateConfigTest

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-07-10 : 05:31:17

I am going to leave it for now and try and get my head round it on Monday. You no what they say new week fresh mind.

Cheers for your help

Whitmoj
If I have inspired one person today then my job is done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources