SQL Server - Update question

Asked By Tang

11-May-07 12:48 AM

Hi
i have a table with a date & week field
the week number is 1,2,3 or 4
so as you scroll down the date there is 7 1's then 7 2's then 7 3's then 7
4's then 7 1's & so on.

This process starts from a specific date.

is there any way to do an update statement on this ?

Thank you

11-May-07 01:52 AM

In the following example the start date for week 1 is 2001-01-01.

UPDATE Calendar
SET WeekNum = FLOOR(DATEDIFF(DAY,'20010101',CalDate)/7.0);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

11-May-07 02:34 AM

thank you David,

i cant see where the range goes bact to 1 after doing 7 lots of 1, then 7
lots of 2 then seven lots of 3, then seven lots of 4

the weeks are 1,2,3,4,1,2,3,4 etc

regards

todd

11-May-07 03:08 AM

Hi, Tango

Try something like this:

UPDATE Calendar SET Week=
(DATEDIFF(d,DATEADD(m,DATEDIFF(m,0,Date),0),Date)+1)/7+1

Razvan

11-May-07 03:22 AM

thanks for the prompt responses, ireally appreciate it

i still cant see how the query knows when to stop at 4 & go back to 1

regards

todd

11-May-07 09:54 AM

Maybe you want something like this:

UPDATE Calendar SET Week=(DATEDIFF(d,'20070201',Data)/7%4)+1

(you can change 1 feb 2007 with the desired date)

Razvan