SQL Server Calculation date

Find an article on the internet, pretty good Oh O (∩_∩) o~

This is the SQL script that calculates the first day of the one month:
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)--the first day of the month

SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ())-1, 0)-the first day of the month

SELECT DATEADD (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))--the last day of the month

SELECT DATEADD (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()), 0))--Last day of last month

Let's separate the statement to see how it works. The core function is getdate (), which most people know is a function that returns the current date and time. The next executed function, DATEDIFF (Mm,0,getdate ()), is the number of months between the date of the current date and "1900-01-01 00:00:00.000". Remember: Time and time variables, like milliseconds, are calculated starting with "1900-01-01 00:00:00.000". That's why you can specify the first time expression as "0" in the DateDiff function. The next function is DATEADD, increasing the number of months from the current date to "1900-01-01". By adding the predefined date "1900-01-01" and the number of months of the current date, we can get the first day of the month. In addition, the time portion of the calculated date will be "00:00:00.000".

The trick is to calculate the time interval of the current date to "1900-01-01" and then add it to "1900-01-01" to get a special date, a technique that can be used to calculate many different dates. The next example is to use this technique to produce different dates from the current date.

Monday of the Week

Here I use the time interval of week (wk) to calculate which day is the Monday of this week.

SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

The first day of the year

The first day of the year is now displayed with a time interval of year (yy).

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 tells you how to do it.

SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

the night of the day

Once it was necessary to intercept the time portion by the GETDATE () function in order to return the time value, it would take into account whether the current date is in the middle of the night. If so, this example uses the DateDiff and DATEADD functions to get a midnight point in time.

SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

In-depth datediff and DateAdd function calculations

You can understand that by using simple DateDiff and DateAdd function calculations, you can find many different dates that may be meaningful.
All the examples so far just calculate the time interval between the current time and "1900-01-01" and then add it to the "1900-01-01" interval to calculate the date. Assuming you modify the number of intervals, or use different intervals to invoke the DateAdd function, or subtract the time interval instead of the increment, you can find and vary the dates by these small adjustments.

Here are four examples using another DateAdd function to calculate the last day before and after two time intervals to replace the DATEADD function respectively.
the last day of last month

This is an example of calculating the last day of last month. It is obtained by subtracting 3 milliseconds from this example on the last day of one months. One thing to keep in mind is that the time in SQL Server is exactly 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

The time portion of the calculated date contains the time at which SQL Server can record the last moment of the day ("23:59:59:997").

last day of last year
To get to the last day of last year, you need to subtract 3 milliseconds from the first sky this year.

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))

last day of the month

Now, in order to get the last day of the month, I need to revise a little bit to get the last day of last month's statement. The modification needs to be added 1 to the time interval returned by the DateDiff comparison between the current date and "1900-01-01". By adding 1 months, I calculated the first day of the next month and then subtracted 3 milliseconds, so that the last day of the month was calculated. This is the SQL script that calculates the last day of the month.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

Select DATEADD (Mm,1,getdate ())-1

the last day of the year
You should now master this practice, which is to calculate the last day of the year script

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0)).

the first Monday of the month

Well, now is the last example. Here I'm going to calculate the first Monday of this month. This is the computed script.
Select DATEADD (wk, DATEDIFF (wk,0, DATEADD (Dd,6-datepart ()), Day,getdate ()), 0)

In this example, I used the script "This week's Monday" and made a little change. The modified part is to replace the "getdate ()" section of the original script with the 6th day of the month, in the calculation with the 6th day of the month to replace the current date so that the calculation can obtain the first Monday of this month.

Summary

I hope these examples will give you a little inspiration when you use the DATEADD and DATEDIFF functions to calculate dates. By using this mathematical method of calculating the time interval of the date, I found it worthwhile to display a useful calendar of intervals between two dates. Note that this is just one way to calculate these dates. Keep in mind that there are many other ways to get the same results. If you have other methods, it's good, if you don't, I hope these examples can give you some inspiration when you want to use the DATEADD and DATEDIFF functions to calculate the date your program may use.
Appendix: Summary of processing methods for some dates

the first day of last month
SELECT DATEADD (mm, DATEDIFF (mm, 0,getdate ())-2, 0)
the last day of last month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

first day of the month
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)
first day of the quarter
SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)
first day of the week
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)
The first day of this year
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)
Take it off today and seconds.
SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)
last day of the month
1. SELECT DATEADD (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

2. SELECT DATEADD (Dd,-day (getdate ()), DATEADD (M,1,getdate ()))
first Monday of the month
Select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)
last day of last month
SELECT DATEADD (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))
last day of last year
SELECT DATEADD (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))
Get rid of time division seconds
DECLARE @ datetime
SET @ = GETDATE ()--' 2003-7-1 10:00:00 '
SELECT @,dateadd (Day, DATEDIFF (day,0,@), 0)
today is the day of the week
Select Datename (Weekday,getdate ())
get the number of days in a month
DECLARE @y varchar (8)
DECLARE @m int
Set @y= ' 2004 '
Set @m=2--month
Select DATEDIFF (day,@y+ '-' +cast (@m as varchar) + ' -15 ', @y+ '-' +cast (@m+1 as varchar) + '-15 ')
determine if a leap year
SELECT Case Day (DateAdd (Mm,2,dateadd (Ms,-3,dateadd (yy, DATEDIFF (), 0))) and then ' yy,0,getdate ' Else ' Leap Year ' end
-OR
Select Case DATEDIFF (Day,datename (Year,getdate ()) + ' -02-01 ', DateAdd (Mm,1,datename (Year,getdate ()) + '-02-01 '))
When-then ' common year ' else ' Leap Year ' end
How many days 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 Ten End
Select @time =datename (year,getdate ()) + '-' +convert (varchar), @m) + '-01 '
Select DateDiff (Day, @time, DateAdd (mm,3, @time))

SQL Server Calculation date