sql server - SQL table with Day, Date, Month, Year, Period, Week Number? (SQL) -

i had created table long time ago using sql had day, month, year, weekday, date, , period (example: april 2016). current table looks like:

|   period   | day | month | year | weekday |    date   | |:-----------|-----|-------|------|---------|----------:| | april 2016 | 21  | april |2016  |thursday |2016-04-21 | 

now needing add week (it week 1, 2,... of current month).

this select statement gives correct result:

select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, getdate()), 0)), 0), getdate() - 1) + 1

this query returns

4 

how insert new column called week existing table , have find current week number?

i believe existing table using getdate() calculate values. unfortunately not have create query anymore.

any appreciated!

first add column week using alter table

alter table tablename add week int 

then update column week number:

update tablename set week = datepart(day, datediff(day, 0, [date])/7 * 7)/7 + 1 

note: week number based on day number alone, not days of week (monday, tuesday) etc.