T-SQL common week settings

--  On the first day of the week, calculate the first day of the week of the given date (Sunday is the First Day)  Declare   @ Date    Datetime  Set   @ Date  =   Getdate  ()  --  SQL Server language versionAlgorithm  --  Train of Thought: the number of days between the current date + Sunday (1st days per week) and the current date  -- The returned value of datepart (weekday, date) is related to @ datefirst.  Set Datefirst 7   --  Or set it to American English set language us_english; (Sunday is the First Day)  Select   Dateadd (Weekday, 1  -  Datepart (Weekday, @ Date ), @ Date ) As  The first day of the week --  Sunday, independent of the SQL Server language version or @ datefirst  --  1899-12-31 is Sunday, 1899-12-31 plus (number of weeks between the current date and 1899-12-31)  Select   Dateadd (Week, Datediff (Week, -  1 , @ Date ), -  1 ) As  Sunday of the week --  Or  Select   Dateadd (Week, Datediff (Week, 6 , @ Date ), 6 ) As  Sunday of the week  Go  --  The second day of the week. Calculate the first day of the week of the given date (Sunday is the first day)  Declare   @ Date   Datetime  Set   @ Date  =   Getdate  ()  --  The returned value of datepart (weekday, date) is related to @ datefirst.  Set Datefirst 7   --   Select   Dateadd ( Day , 2 -  Datepart (Weekday, @ Date ), @ Date ) As  The second day of the week  --  '2017-01-01 'is Monday, '2017-01-01' plus (number of weeks between the current date and '2017-01-01 ')  Select   Dateadd (Week, Datediff (Week, 0 , @ Date ),0 ) As  Monday of the week  Go  --  On the first day of the last week, calculate the last Sunday of the week where the given date is located (Sunday is the first day)  Declare   @ Date    Datetime  Set   @ Date  =   Getdate  ()  --  Train of Thought: The current log is located on Sunday of the week and then minus one week --  The returned value of datepart (weekday, date) is related to @ datefirst.  --  Set datefirst 7 -- or set it to set language us_english; (Sunday is the First Day)  Select   Dateadd (Week, -  1 , Dateadd ( Day , 1  -  Datepart (Weekday, @ Date ), @ Date )) As  First day of last week  --  7 days a week  Select   Dateadd ( Day , -  7 , Dateadd ( Day , 1  -  Datepart (Weekday, @ Date ), @ Date )) As  First day of last week  --  Simplified  Select   Dateadd ( Day , -  6  -  Datepart (Weekday, @ Date ), @ Date )As  First day of last week  --  Last Sunday, it has nothing to do with the SQL Server language version or @ datefirst  Select   Dateadd (Week, -  1  +  Datediff (Week, -  1 , @ Date ), -  1 )As  Last Sunday  --  Or  Select   Dateadd (Week, Datediff (Week, 6 , @ Date ), -  1 ) As  Last Sunday  Go  -- The first day of the next week. Calculate the next Sunday of the week where the given date is located (Sunday is the first day)  Declare   @ Date    Datetime  Set   @ Date  =   Getdate  ()  Set Datefirst 7   Select   Dateadd (Week, 1 , Dateadd (Day , 1  -  Datepart (Weekday, @ Date ), @ Date )) As  First day of next week  --  7 days a week  Select   Dateadd ( Day , 7 , Dateadd ( Day , 1  -  Datepart (Weekday, @ Date ), @ Date )) As  First day of next week  --  Simplified  Select   Dateadd ( Day , 8  - Datepart (Weekday, @ Date ), @ Date ) As  First day of next week  --  Next Sunday, it is irrelevant to the SQL Server language version or @ datefirst  Select   Dateadd (Week, 1  +  Datediff (Week, -  1 ,@ Date ), -  1 ) As  Next Sunday  --  Or  Select   Dateadd (Week, Datediff (Week, -  1 , @ Date ), 6 ) As Next Sunday  Go  --  Determine the day of the week for a given date  Declare   @ Date    Datetime  Set   @ Date  =   Getdate  ()  --  The returned value of datepart (weekday, date) is related to @ datefirst.  Set Datefirst 7  --  Or set it to American English set language us_english; (Sunday is the First Day)  Select   Datepart (Weekday, @ Date ) --  Return Value: 1-Sunday, 2-Monday, 3-Tuesday ...... 7-Saturday  --  The above algorithm is related to the SQL language version or @ datefirst.  --  The following algorithm is irrelevant to the SQL Server language version or @ datefirst.  Select   Datename (Weekday, @ Date ) Week  Go