Time Algorithm in SQLServer

DECLARE @ DateDATETIMESET @ DateGETDATE () -- previous DAY, previous DAY of given Date SELECTDATEADD (DAY,-1, @ Date) AS previous DAY -- next DAY, next DAY of given Date SELECTDATEADD (DAY, 1, @ Date) AS the next day GO -- the beginning of the month, calculate the first day of the month of the given Date -- this computation technique is to calculate first

DECLARE @ Date datetime set @ Date = GETDATE () -- previous DAY, select dateadd (DAY,-1, @ Date) AS 'previous day' -- next DAY, select dateadd (DAY, 1, @ Date) AS 'Next DAY 'go -- the beginning of the month, calculate the first DAY of the month of the given Date -- this computation technique is first calculated

DECLARE @ Date DATETIME

SET @ Date = GETDATE ()

-- The day before, the day before 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'

-- Simplified algorithm. According 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 between the first day of a month and the specified date from the given date

Select dateadd (DAY, 1-DATEPART (DAY, @ Date), @ Date)

GO

-- Calculate the last day of the month of the given date.

DECLARE @ Date DATETIME

SET @ Date = GETDATE ()

-- Train of thought: minus one day for the next month, January 1, January 1

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 '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'

-- Preserve the hour/minute/second Algorithm

Select dateadd (DAY,-1, DATEADD (MONTH, 1, DATEADD (DAY, 1-DATEPART (DAY, @ Date), @ Date )))

GO

-- Computing for other months

-- Calculate the first day of the month of the given date.

DECLARE @ Date DATETIME

SET @ Date = GETDATE ()

-- The first day of the current month minus one 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 '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 of last MONTH'

GO

-- Calculate the last day of the month of the given date.

DECLARE @ Date DATETIME

SET @ Date = GETDATE ()

-- The first day of the current month minus one day

Select dateadd (DAY,-1, DATEADD (MONTH, DATEDIFF (MONTH, 0, @ Date), 0) AS 'last DAY of last 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 last month'

Select dateadd (DAY, 1-DATEPART (DAY, @ Date), @ Date)-1 'last DAY of last 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.

-- Select dateadd (MONTH, 1, '1970-06-30 ') -- The result is instead,

-- This is why the last-month algorithm minus one day next month

-- If the calculation month is 31 days, this problem does not occur.

-- For example, select dateadd (MONTH, 1, '2017-05-31 ') -- The result is 2010

-- Therefore, the following algorithm is correct.-1 indicates '2017-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 last 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

-- Calculate the first day of the week for 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: the number of days between the current date + Sunday (1st days per week) and 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, regardless of the SQL Server language version or @ DATEFIRST

-- '2017-12-31 'is Sunday, '2017-12-31' plus (number of weeks between the current date and)

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 2nd day of the week (Sunday is the first day) of the given date)

DECLARE @ Date DATETIME

SET @ Date = GETDATE ()

-- Train of thought: the number of days between the current date + Monday (2nd days per week) and 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 (DAY, 2-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of the week, Monday'

-- Monday is irrelevant to the SQL Server language version or @ DATEFIRST

-- '2014-01-01 'is Monday, '2014-01-01' plus (number of weeks between the current date and)

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

-- 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 (WEEK,-1, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of last WEEK, Sunday'

-- One week equals 7 days

Select dateadd (DAY,-7, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of last week, Sunday'

-- Simplified

Select dateadd (DAY,-6-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of last week, Sunday'

-- 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

-- 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 ()

-- Idea: add one week to the Sunday of the Current Log

-- 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 (WEEK, 1, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of next WEEK, Sunday'

-- One week equals 7 days

Select dateadd (DAY, 7, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of next week, Sunday'

-- Simplified

Select dateadd (DAY, 8-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of next week, Sunday'

-- 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 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 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 computing

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'

-- Calculate the last day of the year at the end of the previous year

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 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 computing

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'

-- Beginning of 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'

-- Beginning of 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'

-- 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 ()

-- The 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) -- the first day of this QUARTER

Select datediff (DAY, @ Date, DATEADD (QUARTER, 1, @ Date ))

GO

-- Calculates the number of days of the year of a given date.

DECLARE @ Date DATETIME;

SET @ Date = GETDATE ()

-- The 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 a given date is a leap year.

-- Judge 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 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

-- Calculates 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]; -- returns the 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 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

-- Calculates the day of the first Monday of the month of the given date.

DECLARE @ Date DATETIME;

SET @ Date = GETDATE ()

-- Idea: add the week (Monday) (the week Number Difference Between the month 6 of the given log and)

-- 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