[Post] SQL Server various date calculation methods (favorites)

Generally, you need to obtain the current date and calculate some other dates. For example, your program may need to determine the first or last day of a month. Most of you probably know how to divide a date (year, month, day, and so on ), then, just use the split year, month, and day to calculate the date you need in several functions! In this article, I will show you how to use the dateadd and datediff functions to calculate different dates that may be used in your program.

Before using the examples in this article, you must pay attention to the following issues. Most examples may not have different execution results on different machines, which is determined by the day of the week. The first day (datefirst) determines the day your system uses as the first day of the week. All of the following examples are created on Sunday as the first day of the week, that is, the first day of the week is set to 7. If your first day settings are different, you may need to adjust these examples to make them consistent with the previous day settings. You can use the @ datefirst function to check the settings on the first day.
　　
To understand these examples, let's first review the datediff and dateadd functions. The datediff function calculates the total number of time intervals between two dates, such as hour, day, week, month, and year. The dateadd function calculates a date and obtains a new date by adding or subtracting the time interval. For more information about datedi ff and dateadd functions and their time interval, read the Microsoft online help.
　　
Using the datediff and dateadd functions to calculate a date is a bit different from the method used to convert from the current date to the date you need. You must consider the time interval. For example, how many time intervals are there between the current date and the date you want to get, or between today and a certain day (such as-1-1. Understanding how to focus on Time Interval helps you easily understand my different date calculation examples.
　　
The first day of a month
　　
In the first example, I will show you how to get to the last day of the month from the current date. Note: This example and other examples in this article will only use the datediff and dateadd functions to calculate the date we want. In each example, the date to be calculated is obtained through calculation but the previous time interval, and then addition or subtraction is performed.
　　
　　This is the SQL script used to calculate the first day of a month:
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
　　
Let's split this statement to see how it works. The core function is getdate (). Most people know that this is a function that returns the current date and time. The datediff (mm, 0, getdate () function executed next is used to calculate the number of months between the current date and the date "00:00:00. 000. Remember: The period and time variables start from 00:00:00. 000, just like milliseconds. This is why you can specify the first time expression as "0" in the datediff function ". The next function is dateadd, which adds the number of months from the current date to "1900-01-01. By adding the predefined date "" and the number of months of the current date, we can get the first day of this month. In addition, the time part of the calculated date will be "00:00:00. 000 ".
　　
The 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. The next example uses this technique to generate different dates from the current date.
Monday of this week
　　
　　Here, we use the weekly (WK) interval to calculate the day of the week.　　
Select dateadd (wk, datediff (wk, 0, getdate (), 0)
　　
　　The first day of a year
　　
Now we use the interval of year (yy) to display the first day of the year.
　　
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
　　
　　First day of the quarter
　　
If you want to calculate the first day of the quarter, this example shows you how to do it.
　　
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
　　
Midnight of that day
　　
In the past, the getdate () function was used to cut off the time part in order to return the time value. It will take into account whether the current date is in the middle of the night. In this example, the datediff and dateadd functions are used to obtain the midnight time point.
　　
Select dateadd (DD, datediff (DD, 0, getdate (), 0)
　　
Go deep into datediff and dateadd function compute
　　
You can understand that by using simple datediff and dateadd functions, you can find many different dates that may be meaningful.
　　
All the examples so far only calculate the number of time intervals between the current time and "", and then add it to the "" time interval to calculate the date. If you modify the number of time intervals, or use different time intervals to call the dateadd function, or subtract the time interval instead of increasing, you can find and have different dates through these small adjustments.
　　
Here are four examples of using another dateadd function to calculate the last day to replace the two time intervals before and after the dateadd function.
Last day of last month
　　
This is an example of calculating the last day of the last month. It is obtained by subtracting 3 ms from the last day of the month. Remember that the time in SQL Server is accurate to 3 ms. That's why I need 3 milliseconds to get the date and time I want.
　　
Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))
　　
The time part of the calculated date contains the time of the last day ("23: 59: 59: 997") that an SQL server can record.
　　
　　Last day of last year
　　
In the above example, to get the last day of last year, you need to subtract 3 ms from the first day of this year.
　　
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
　　
　　Last day of this month
　　
Now, in order to get the last day of this month, I need to slightly modify the statements to get the last day of the previous month. For modification, you need to use datediff to compare the time interval returned by the current date and "" with 1. By adding a month, I calculate the first day of the next month, and then subtract 3 milliseconds, so that the last day of the month is calculated. This is the SQL script used to calculate the last day of the month.
　　
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))
　　
　　Last day of this year 　　
You should master this practice now. This is the script for calculating the last day of this year.
　　
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 )).
　　
　　The first Monday of this month
　　
Now is the last example. Here I want to calculate the first Monday of this month. This is the computing script.
　　
Select dateadd (wk, datediff (wk, 0,
Dateadd (DD, 6-datepart (day, getdate (), getdate ())
), 0)
　　
In this example, I used the "Monday of this week" script and made some modifications. The modified part is to replace the "getdate ()" part in the original script with the 6th days of this month, in the calculation, the current date is replaced with the 6th day of this month so that the calculation can get the first Monday of this month.
　　
Summary
　　
I hope these examples will give you some inspiration when you use the dateadd and datediff functions to calculate the date. By using this mathematical method for calculating the time interval of a date, I found that useful calendars for displaying the interval between two dates are valuable. Note that this is only one way to calculate these dates. Remember, there are still many ways to get the same computing results. If you have other methods, it would be nice. If you do not have them, I hope these examples will give you some inspiration, when you use the dateadd and datediff functions to calculate the date that your program may use.
Appendix, other date Handling Methods
　　
1) Remove the hour, minute, and second
Declare @ datetime
Set @ = getdate () -- '2017-7-1 10:00:00'
Select @, dateadd (day, datediff (day, 0, @), 0)
　　
2) display the day of the week
Select datename (weekday, getdate ())
　　
3) how to obtain the number of days in a month
Declare @ M int
Set @ M = 2 -- month
Select datediff (day, '2017-'+ Cast (@ M as varchar) +'-15', '2017-'+ Cast (@ m + 1 as varchar) + '-15 ')
In addition, get the number of days this month
Select datediff (day, cast (month (getdate () as varchar) + '-' + Cast (month (getdate () as varchar) + '-15 ', cast (month (getdate () as varchar) + '-' + Cast (month (getdate () + 1 as varchar) + '-15 ')
Or use the script to calculate the last day of the month, and then use the day function area for the last day.
Select Day (dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 )))
　　
4) determine whether a leap year is used:
Select case day (dateadd (mm, 2, dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 )))) when 28 then 'Year' else 'Year' end
Or
Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate ()) + '-02-01 '))
When 28 then 'Year' else 'Year' end
　　
5) How many days of a quarter
Declare @ M tinyint, @ time smalldatetime
Select @ M = month (getdate ())
Select @ M = case when @ m between 1 and 3 then 1
When @ m between 4 and 6 then 4
When @ m between 7 and 9 then 7
Else 10 end
Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ M) + '-01'
Select datediff (day, @ time, dateadd (mm, 3, @ time ))