Time calculation statements in SQL Server SQL

Q: How do you calculate the week start and the due date in a table and write to a field? I want to import data from one table to another and convert it with VB.

I now have a table the main fields have

time_id int
time_date datetime
year int
week_of_year int
day nvarharWant to convert to another table

time_id int
time_date datetime
year int
week_of_year nvarcharThe original table content is

--------------------
1 2003-07-09 2003 20 星期日
1 2003-07-10 2003 20 星期一
1 2003-07-11 2003 20 星期二

Want to become

--------------------
1 07/09/2003 2003 第20周（7/9-7/17）
1 07/10/2003 2003 第20周（7/9-7/17）
1 07/11/2003 2003 第20周（7/9-7/17）Please expert advice, this statement should be how to write? For:

if object_id (' tablename ') is not null drop TABLE tablename
Select 1 as time_id, ' 2003-07-09 ' as time_date, 200 3 as [year], the as week_of_year, ' Sunday ' as [day]
into tablename
Union select 1, ' 2003-07-10 ', 2003, 20, ' Monday '
Unio N Select 1, ' 2003-07-11 ', 2003, 20, ' Tuesday '
------------------------------------------------
Select time_id, time_ Date, [year], ' first ' + cast (week_of_year as varchar (2)) + ' Week ('
+ cast (month (week_begin) as varchar (2)) + '/' + CAST (day (week_begin) as varchar (2)) + '-'
+ cast (month (week_end) as varchar (2)) + '/' + CAST (day (week_end) as varchar (2)) as Week_of_year
From (SELECT *, DateAdd (day, 1-datepart (weekday, time_date), time_date) as Week_begin,
DateAdd (d Ay, 7-datepart (Weekday, time_date), time_date) as week_end to TableName) a
/*
time_id time_date Year week_of_y Ear
1 2003-07-09 2003 20 Week (7/6-7/12)
1 2003-07-10 2003 7/6-7/12 20 weeks ()
1 2003-07-11 2003/20 weeks (7/6-7 /12)
*/
------------------------------------------------
drop TABLE tablename

Although the problem is solved, but this example is not universal or case, so we analyzed your code and found a problem: How is the date range determined? So, we extend it to: can we set the scope of the date independently? For example, set to start in Monday or Sunday:

　　思路：
SET DATEFIRST
将一周的第一天设置为从 1 到 7 之间的一个数字。
　　 语法
SET DATEFIRST { number | @number_var }
　　 参数
number | @number_var
　　 是一个整数，表示一周的第一天，可以是下列值中的一个。
　　 值 一周的第一天是
1 星期一
2 星期二
3 星期三
4 星期四
5 星期五
6 星期六
7（默认值，美国英语） 星期日
　　
注释
使用 @@DATEFIRST 函数检查 SET DATEFIRST 的当前设置。
　　 SET DATEFIRST 的设置是在执行或运行时设置，而不是在分析时设置。