1

for every row in TableA i have a date and a week number field. The date field is already populated and i was hoping there would be a way to automatically fill the Week number field aswell instead of having to do it manually.

an example of the date is as follows: 20111007

hope you can help, cheers :)

PDB
  • 103
  • 2
  • 3
  • 13
  • Have you tried this?: http://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005 – Jandrejc Sep 02 '11 at 09:45
  • @Jandrejc the answer for that question is not really good. I found a way to write the logic in just 2 lines. – t-clausen.dk Sep 02 '11 at 16:31

1 Answers1

4

With this syntax you can retrieve the week for a date.

SELECT datepart(week, '20111007'), datepart(iso_week, '20111007')

iso_week only works from sqlserver 2008 though.

You really should consider making the week field into a computed column. It will make your life so much easier (I would use this solution).

This would be your syntax:

alter table <yourtable> add week1 as datepart(week, <yourdatecolumn>)

Should you for some reason be prevented from making database changes, you could use this:

UPDATE <table>
SET <weekcolumn> = datepart(week, <yourdate>)
WHERE week is null and <yourdate> is not null

Problem is that if the date change, the week will still remain the same and the week will not be available until this script is used unless the week is updated with a trigger.

You could also create a view to show all columns and add the week as a computed field, this would also be a valid solution. Although it is good practice to list the columns instead of using wildchar(*), I used it because i don't know your columns.

CREATE VIEW v_yourview as
SELECT *, datepart(week, <yourdate>) week1 FROM <table>
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • +1, not for datepart(), but for the real solution: computed column – KM. Sep 02 '11 at 12:34
  • @KM thx for the point and your comment. The datepart demonstration in the beginning of the answer is not part of the solution, it is documentation. – t-clausen.dk Sep 02 '11 at 13:10
  • Great answer, i created a view and it worked great. Thanks alot. – PDB Sep 02 '11 at 20:22