SQL DateAdd and DateDiff functions Tutorial

Let's review the DateDiff and DATEADD functions first. The DateDiff function calculates the total number of hours, days, weeks, months, and years of time between two dates. The DateAdd function calculates a date by adding and reducing time intervals to obtain a new date. Learn more about DateDiff and DATEADD functions and time intervals to read Microsoft online Help.
Using the DateDiff and DATEADD functions to calculate dates is a little different from the way you think about converting from the current date to the date you need. You have to consider this in terms of time intervals. For example, how many time intervals between the current date and the date you want, or how many time intervals between today and one day (such as 1900-1-1), and so on. Understanding how to focus on time intervals can help you easily understand my different examples of date calculations.
The first day of one months
For the first example, I will show you how to go from the current date to the last day of the month. Please note: This example and the other examples in this article will only use the DateDiff and DATEADD functions to calculate the date we want. Each example will get the date you want to calculate by calculating the previous time interval and then adding and reducing it.
This is the SQL script that calculates the first day of the one month:
Select DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)
Let's take this statement apart to see how it works. The most central function is getdate (), which most people know is the function that returns the current date and time. The next executing function DateDiff (Mm,0,getdate ()) is the number of months between the current date and the date "1900-01-01 00:00:00.000". Keep in mind: time and time variables, like milliseconds, are calculated from "1900-01-01 00:00:00.000". That's why you can specify the first time expression in the DateDiff function to be "0". 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 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 also uses this technique to produce a different date from the current date.
Monday of the Week
Here I use the WK time interval to calculate which day is this week's Monday.

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

The first day of the year
The first day of the year is now displayed with the year (yy) interval.
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 what to do.

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

The middle of the day
The GETDATE () function was used to truncate the time section to return the time value, taking 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.
Select DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

Deep DateDiff and DateAdd function calculation
You can see that by using simple DateDiff and DateAdd function calculations, you can find many different dates that may be meaningful.
All the examples so far are just calculating the current time and the number of time intervals between "1900-01-01" and then adding it to the "1900-01-01" time interval to calculate the date. Suppose you modify the number of time intervals, or use different time intervals to invoke the DateAdd function, or subtract the time interval instead of increasing, then you can find and how many different dates through these small adjustments.
Here are four examples that use another DateAdd function to calculate the last day to replace the DATEADD function two intervals before and after each other.

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 the example on the last day of one months. One thing to keep in mind is that in SQL
The time in the server is accurate to 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 a SQL
The time that the server can record the last moment of the day ("23:59:59:997").

Last day of last year
To connect the above example, to get the last day of last year, you need to subtract 3 milliseconds from the first sky of the year.

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

The 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. Modifications need to be added 1 to the time interval between the current date and the "1900-01-01" comparison with DateDiff. By adding 1 months, I figure out the first day of the next month, then subtract 3 milliseconds, so I can figure out the last day of the month. 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))

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 this month
Well, now is the last example. Here I want 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 ()), GETDATE ()), 0)
In this example, I used the "This week's Monday" script and made a little change. Part of the change is to replace the "getdate ()" section of the original script with the 6th day of the month, replacing the current date with the 6th day of the month so that the calculation gets the first Monday of the month.
Summarize
By using this mathematical method of calculating the time interval of the date, I found it valuable to show the useful calendars between the two dates. Note that this is just one way to figure out these dates. Keep in mind that there are many ways to get the same results. If you have other methods, that's great, and if you don't, I hope these examples will give you some inspiration when you want to use the DATEADD and DATEDIFF functions to calculate the dates that your program might use.

---------------------------------------------------------------

Appendix, other date processing methods

1 Remove time and seconds
DECLARE @ datetime
SET @ = getdate ()-' 2003-7-1 10:00:00 '
Select @,dateadd (Day, DATEDIFF (day,0,@), 0)

2 shows the day of the week
Select Datename (Weekday,getdate ())

3 How to get the number of days in a month
DECLARE @m int
Set @m=2--month
Select DateDiff (Day, ' 2003-' +cast) (@m as
varchar) + ' -15 ', ' 2003-' +cast (@m+1 as
varchar) + '-15 ')
Also, 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 that calculates the last day of the month, and then use the day function area last
Select Day (DateAdd ms,-3,dateadd (mm,
DATEDIFF (M,0,getdate ()) +1, 0))

4) to determine whether leap year:
Select Case Day (DATEADD (mm, 2,
DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()),
0)) When then ' excepting ' else ' leap Year ' end
Or
Select Case
DateDiff (Day,datename (Year,getdate ()) + ' -02-01 ', DateAdd (Mm,1,datename (Year,getdate ()) + '-02-01 ')

When then ' excepting ' else ' leap Year ' end

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