Beginners must read: time computing statements in SQL-MySQL

Beginners must read: time computing statements in SQL: how can I calculate the start and end dates of a week in a table and write them to the table fields? I want to import data from one table to another and convert the data using VB.

I have a table with the main fields:

Time_id int

Time_date datetime

Year int

Week_of_year int

Day nvarhar

Want to convert to another table

Time_id int

Time_date datetime

Year int

Week_of_year nvarchar

The original table content is

--------------------

1 Sunday 2003

1 2003 Monday

1 2003 Tuesday

To change

--------------------

1 07/09/2003 2003 Week (20th-7/9)

1 07/10/2003 2003 Week (20th-7/9)

1 07/11/2003 2003 Week (20th-7/9)

Excuse me: how should I write this statement?

A:

If object_id ('tablename') is not null drop table tablename

Select 1 as time_id, '2017-07-09 'as time_date, 2003 as [year], 20 as week_of_year, 'Sunday' as [day]

Into tablename

Union select 1, '2014-07-10 ', 2003, 20, 'Monday'

Union select 1, '2014-07-11 ', 2003, 20, 'tues'

------------------------------------------------

Select time_id, time_date, [year], 'quarter '+ 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 (day, 7-datepart (weekday, time_date), time_date) as week_end from tablename)

/*

Time_id time_date year week_of_year

1 2003 Week (20th-7/6)

1 2003 week 20th (7/6-7/12)

1 2003 week 20th (7/6-7/12)

*/

------------------------------------------------

Drop table tablename

Although the problem has been solved, this example is not universal or case-based. so we analyzed your code and found a problem: how is the date range determined? Therefore, we extend it to: can we set the date range? For example, it is set to start on Monday or Sunday:

Ideas:

SET DATEFIRST

Set the first day of a week to a number ranging from 1 to 7.

Syntax

Set datefirst {number | @ number_var}

Parameters

Number | @ number_var

Is an integer that indicates the first day of a week. it can be one of the following values.