Time algorithms in SQL Server

No nonsense.CodeThe comment is very detailed. It is certainly okay to understand it!

Declare @ date datetime
Set @ date = getdate ()
-The previous day, the previous day of the given date
Select dateadd (day,-1, @ date) as 'previous Day'
-The next day, the day after the given date
Select dateadd (day, 1, @ date) as 'Next Day'
Go

-Calculate the first day of the month of the given date at the beginning of the month.
-This calculation technique is to calculate the number of time intervals from the current date to "", and then add it to "" to obtain a special date, this technique can be used to calculate many different dates.
Declare @ date datetime
Set @ date = getdate ()
Select dateadd (month, datediff (month, '2017-01-01 ', @ date), '2017-01-01') as 'Day of the month'
-SimplifiedAlgorithmAccording to the time representation of SQL Server, '2017-01-01 'can be replaced by 0.
Select dateadd (month, datediff (month, 0, @ date), 0) as 'Day of the month'
-The preceding two algorithms are accurate to 00:00:00. 000 every day.
-The following algorithm class is used to retain the hour, minute, and second.
-Train of Thought: subtract the number of days from the first day of the month from the given date using the given date
Select dateadd (day, 1-datepart (day, @ date), @ date)
Go

-Calculate the last day of the month of the given date at the end of the month.
Declare @ date datetime
Set @ date = getdate ()
-Train of Thought: in the current month, the next month will be reduced by one day.
Select dateadd (day,-1, dateadd (month, 1 + datediff (month, '2017-01-01 ', @ date), '2017-01-01 ′)) as 'Day of the month'
Select dateadd (month, 1 + datediff (month, '2017-01-01 ', @ date), '2017-01-01')-1 as 'Day of the month'
-Replace 1900-01-01 with 0
Select dateadd (day,-1, dateadd (month, 1 + datediff (month, 0, @ date), 0) as 'Day of the month'
Select dateadd (month, 1 + datediff (month, 0, @ date), 0)-1 as 'Day of the month'
-Idea: Same as the computing idea at the beginning of the month
Select dateadd (month, datediff (month, '2017-12-31 ', @ date), '2017-12-31') as 'the day of the month'
-Simplified algorithm. '2017-12-31 'is replaced by-1.
Select dateadd (month, datediff (month,-1, @ date),-1) as 'Day of the month'
-Retention time and minute-second Algorithm
Select dateadd (day,-1, dateadd (month, 1, dateadd (day, 1-datepart (day, @ date), @ date )))
Go

-Other months

-calculate the first day of the month of the given date
declare @ date datetime
set @ date = getdate ()
-minus one month on the first day of the current month
select dateadd (month,-1, dateadd (month, datediff (month, 0, @ date), 0 )) as 'first day of last month'
-Simplified
select dateadd (month, datediff (month, 0, @ date) as 'first day of last month'
-another algorithm for the first day of the current month
select dateadd (month,-1, dateadd (day, 1-datepart (day, @ date ), @ date) 'Day 1 of last month'
go

-calculate the last day of the month of the given date
declare @ date datetime
set @ date = getdate ()
-minus one day on the first day of the current month
select dateadd (day,-1, dateadd (month, datediff (month, 0, @ date), 0 )) as 'last day of the Month'
-another algorithm for the first day of the current month
select dateadd (day,-1, dateadd (day, 1-datepart (day, @ date ), @ date) 'last day of the Month'
select dateadd (day, 1-datepart (day, @ date), @ date) -1 'last day of the Month'
-in another algorithm, the last day of the current month cannot be used to add a month, because the current month may be 30 days.
-for example, select dateadd (month, 1, '2017-06-30 ')-the result is instead,
-this is also the reason why the last day of the month algorithm minus one day of the next month
-however, if the calculation month is 31 days, this problem does not exist
-for example, select dateadd (month, 1, '1970-05-31 ')-the result is
-the following algorithm is correct.-1 indicates '1970-12-31 00:00:00. 000 '-select convert (datetime,-1)
select dateadd (month, datediff (month,-1, @ date)-1,-1)
-another current month algorithm
select dateadd (day,-1, dateadd (day, 1-datepart (day, @ date), @ date )) 'Last day of the Month'
-Simplified
select dateadd (day, 0-datepart (day, @ date), @ date) 'Last day of last month'
go

-Calculate the first day of the next month for the given date.
Declare @ date datetime
Set @ date = getdate ()
-Add one month on the first day of the current month
Select dateadd (month, 1, dateadd (month, datediff (month, 0, @ date), 0) as 'first day of next month'
-Simplified
Select dateadd (month, datediff (month, 0, @ date) +) as 'Day of next month'
-Another Algorithm for the first day of the current month
Select dateadd (month, 1, dateadd (day, 1-datepart (day, @ date), @ date) 'Day of next month'
Go

-Calculate the last day of the next month of the given date.
Declare @ date datetime
Set @ date = getdate ()
-The first day of the current month plus two months and then minus one day
Select dateadd (day,-1, dateadd (month, 2, dateadd (month, datediff (month, 0, @ date), 0) as 'last day of next month'
-Simplified
Select dateadd (day,-1, dateadd (month, datediff (month, 0, @ date) + 2, 0) as 'last day of next month'
Select dateadd (month, datediff (month, 0, @ date) + 2, 0)-1 as 'last day of next month'
-Another Algorithm
Select dateadd (month, datediff (month,-1, @ date) + 1,-1) 'last day of next month'
-Another Algorithm for the first day of the current month
Select dateadd (day,-1, dateadd (month, 2, dateadd (day, 1-datepart (day, @ date), @ date) 'last day of next month'
Go

-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 ()
-algorithms related to the SQL Server language version
-train of thought: current date + Sunday (1st days per week) the number of days before the current date
-the return 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 (weekday, 1-datepart (weekday, @ date), @ date) as 'Day of the week, Sunday'
-Sunday, it has nothing to do with the SQL Server language version or @ datefirst
-'2017-12-31 'is Sunday, '2017-12-31' plus (number of weeks between the current date and) weeks
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

-calculate the second day of the week, and calculate the 2nd day of the week where the given date is located (Sunday is the first day)
declare @ date datetime
set @ date = getdate ()
-train of thought: number of days between the current date + Monday (2nd days per week) and the current date
-datepart (weekday, date) the returned value is related to @ datefirst
set datefirst 7-or set to set language us_english; (Sunday is the first day)
select dateadd (day, 2-datepart (weekday, @ date), @ date) as 'Day of the week, Monday'
-Monday, it has nothing to do with the SQL Server language version or @ datefirst
-'2017-01-01 'is Monday, '2017-01-01' plus (the number of weeks between the current date and) weeks
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: minus one week on Sunday of the week where the current log is located
-datepart (weekday, date) the returned value is related to @ datefirst
set datefirst 7-or set to set language us_english; (Sunday is the first day)
select dateadd (week,-1, dateadd (day, 1-datepart (weekday, @ date), @ date) as 'Day of the last week, sunday'
-a week equals 7 days
select dateadd (day,-7, dateadd (day, 1-datepart (weekday, @ date), @ date )) as 'first day of last week, Sunday'
-Simplified
select dateadd (day,-6-datepart (weekday, @ date), @ date) as 'first day of last week, Sunday'
-last Sunday, independent of 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

-on 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 ()
-train of thought: add one week to the Sunday of the current log in the week
-datepart (weekday, date) the returned value is related to @ datefirst
set datefirst 7-or set to set language us_english; (Sunday is the first day)
select dateadd (week, 1, dateadd (day, 1-datepart (weekday, @ date), @ date) as 'Day of the next week, sunday'
-a week equals 7 days
select dateadd (day, 7, dateadd (day, 1-datepart (weekday, @ date), @ date )) as 'first day of next week, Sunday'
-Simplified
select dateadd (day, 8-datepart (weekday, @ date), @ date) as' first day of next week, sunday'
-next Sunday, independent of 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 ()
-datepart (weekday, date) the returned value is related to @ datefirst
set datefirst 7-or set to 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

-Annual calculation
Declare @ date datetime
Set @ date = getdate ()
-Calculate the first day of the year of the given date at the beginning of the year.
Select dateadd (year, datediff (year, 0, @ date), 0) as 'Day of the year'
-Calculate the last day of the year of the given date at the end of the year.
Select dateadd (year, datediff (year,-1, @ date),-1) as 'last day of the year'
-Calculate the first day of the year in which the given date is located at the beginning of the previous year.
Select dateadd (year, datediff (year,-0, @ date)-) as 'Day of the previous year of the year'
-At the end of the previous year, calculate the last day of the year of the given date.
Select dateadd (year, datediff (year, 0, @ date),-1) as 'the last day of the previous year'
-Calculate the first day of the next year in the year of the given date.
Select dateadd (year, 1 + datediff (year, 0, @ date), 0) as 'Day of the next year of the year'
-Calculate the last day of the next year of the year at the end of the next year.
Select dateadd (year, 1 + datediff (year,-1, @ date),-1) as 'the last day of the next year'
Go

-Quarterly Calculation
Declare @ date datetime
Set @ date = getdate ()
-Calculate the first day of the quarter of the given date at the beginning of the quarter
Select dateadd (quarter, datediff (quarter, 0, @ date), 0) as 'first day of the current quarter'
-Calculate the last day of the quarter of the given date at the end of the quarter
Select dateadd (quarter, 1 + datediff (quarter, 0, @ date),-1) as 'last day of the current quarter'
-Early last quarter
Select dateadd (quarter, datediff (quarter, 0, @ date)-1, 0) as 'beginning of the last quarter of the current quarter'
-End of the last quarter
Select dateadd (quarter, datediff (quarter, 0, @ date),-1) as 'end of last quarter of the current quarter'
-Early next quarter
Select dateadd (quarter, 1 + datediff (quarter, 0, @ date), 0) as 'beginning of the next quarter of the current quarter'
-End of next quarter
Select dateadd (quarter, 2 + datediff (quarter, 0, @ date),-1) as 'end of next quarter of the current quarter'
Go
-Calculates the number of days in the month of a given date.
Declare @ date datetime;
Set @ date = getdate ()
-The number of days between the first day of the month and the next day of the month
Select datediff (day, dateadd (month, datediff (month, 0, @ date), 0), dateadd (month, 1 + datediff (month, 0, @ date ), 0 ))
-Simplify with variables
Select @ date = dateadd (month, datediff (month, 0, @ date), 0)-the first day of the month
Select datediff (day, @ date, dateadd (month, 1, @ date ))
-Another idea: specify the date of the last day of the month as the number of days of the month.
Select Day (dateadd (month, datediff (month,-1, @ date),-1 ))
Go

-Calculate the number of days in the quarter of a given date
Declare @ date datetime;
Set @ date = getdate ()
-Number of days between the first day of the current quarter and the first day of the next quarter
Select datediff (day, dateadd (quarter, datediff (quarter, 0, @ date), 0), dateadd (quarter, 1 + datediff (quarter, 0, @ date ), 0 ))
-Simplify with variables
Select @ date = dateadd (quarter, datediff (quarter, 0, @ date), 0)-First day of this quarter
Select datediff (day, @ date, dateadd (quarter, 1, @ date ))
Go

-Calculate the number of days in the year of the given date.
Declare @ date datetime;
Set @ date = getdate ()
-Number of days between the first day of the current year and the first day of the next year
Select datediff (day, dateadd (year, datediff (year, 0, @ date), 0), dateadd (year, 1 + datediff (year, 0, @ date ), 0 ))
-Simplify with variables
Select @ date = dateadd (year, datediff (year, 0, @ date), 0)-the first day of the year
Select datediff (day, @ date, dateadd (year, 1, @ date ))
Go

-Determines whether the year of the given date is a leap year.
-Determine based on the total number of days of the year
Declare @ date datetime;
Set @ date = getdate ()
Select case datediff (day, dateadd (year, datediff (year, 0, @ date), 0), dateadd (year, 1 + datediff (year, 0, @ date ), 0 ))
When 365 then 'Year' else 'Year' end
-Judge based on the number of days in December February
-Add two months to the last day of the last year of the date, that is, the last day of the year, January 1, February.
Select case day (dateadd (month, 2, dateadd (year, datediff (year, 0, @ date),-1) when 28 then 'Year' else 'Year' end
Go

-Calculate the day of the year for a given date.
Declare @ date datetime;
Set @ date = getdate ()
Select datepart (dayofyear, @ date) [dayofyear];
Select datename (dayofyear, @ date) [dayofyear];
-Another idea: the number of days between the current date and the last day of the previous year
Select datediff (day, dateadd (year, datediff (year, 0, @ date),-1), @ date) [dayofyear]
Go

-Calculate the week number of the year for a given date.
Declare @ date datetime;
Set @ date = getdate ()
Select datepart (Week, @ date) [weekofyear];-return int type
Select datename (Week, @ date) [weekofyear];-return varchar type
Go

-Calculate the month of the current year for a given date.
Declare @ date datetime;
Set @ date = getdate ()
Select datepart (month, @ date) [monthofyear];-return int type
Select datename (month, @ date) [monthofyear];-return varchar type
Select month (@ date) [monthofyear];-return int type
Go

-Calculate the quarter of the year for a given date
Declare @ date datetime;
Set @ date = getdate ()
Select datepart (quarter, @ date) [quarterofyear];-return int type
Select datename (quarter, @ date) [quarterofyear];-returns the varchar type
Go

-Calculate the week number of the current month for a given date.
Declare @ date datetime;
Set @ date = getdate ()
-Train of Thought: The given date is the week of the current year-the first day of the given date is the week of the current year
Select datepart (Week, @ date)-datepart (Week, dateadd (month, datediff (month, 0, @ date), 0) + 1 [weekofmonth]
Select datepart (Week, @ date)-datepart (Week, dateadd (day, 1-datepart (day, @ date), @ date) + 1 [weekofmonth]
Go

-Calculate the day of the first Monday of the month of the given date.
Declare @ date datetime;
Set @ date = getdate ()
-Train of Thought: add (the week Number Difference Between the month 6 and of the given log) to (Monday)
-Why not select 7? If it is 7, then 7 is Sunday, the first Monday will be counted as 8.
-Why not select "5? If the fifth day is Saturday, Monday is the last month. Smaller than the 5th is the same as this.
Select dateadd (Week, datediff (Week, 0, dateadd (day, 6-datepart (day, @ date), @ date), 0) 'The first Monday of the month'
Select dateadd (Week, datediff (Week, 0, dateadd (day, 6-datepart (day, @ date), @ date), 7) 'Second Monday of the month'
Select dateadd (Week, datediff (Week, 0, dateadd (day, 6-datepart (day, @ date), @ date), 1) 'First Tuesday of the month'
Select dateadd (Week, datediff (Week, 0, dateadd (day, 6-datepart (day, @ date), @ date), 8) 'Second Tuesday of the month'
Go