Shulou(Shulou.com)05/31 Report--

SQL in how to use DATEADD and DATEDIFF, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Usually, you need to get the current date and calculate some other dates. For example, your program may need to determine the first or last day of the month. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the split year, month, day, etc., in a few functions to calculate the date you need! In this article, I'll show you how to use the DATEADD and DATEDIFF functions to calculate some of the different dates you might use in your program. Before using the examples in this article, you must pay attention to the following problems. Most of the examples may not have the same results on different machines, depending on which day is the first day of the week. The DATEFIRST setting determines which day your system uses as the first day of the week. All the following examples are created with Sunday as the first day of the week, that is, the first day is set to 7. If your first day settings are different, you may need to adjust these examples to match the different first day settings. You can check the first day settings through the @ @ DATEFIRST function. To understand these examples, let's review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the total number of hours, days, weeks, months, years, and so on, between two dates. The DATEADD function calculates a date to get a new date by adding or subtracting the interval. To learn more about DATEDIFF and DATEADD functions and intervals, you can read the Microsoft online help. Using the DATEDIFF and DATEADD functions to calculate the date is a little different from the way you would have thought about converting from the current date to the date you need. You must consider it in terms of time interval. For example, how much time is there between the current date and the date you want to get, or between today and a certain day (such as 1900-1-1), and so on. Understanding how to focus on time intervals will help you easily understand my different date calculation examples. For the first example of the first day of a month, I will show you how to get to the last day of the month from the current date. Please note that this and other examples in this article will only use the DATEDIFF and DATEADD functions to calculate the dates we want. Each example will calculate the desired date by calculating the previous time interval, and then adding and subtracting. This is the SQL script that calculates the first day of the month:

Select DATEADD (mm, DATEDIFF (mm,0,getdate ()), 0)

Let's separate this 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 function to be executed, DATEDIFF (mm,0,getdate ()), is to calculate the number of months between the current date and the date "1900-01-01 00 00.000". Remember: the period and time variables, like milliseconds, are calculated starting with "1900-01-01 00-00.000". This is 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 increasing 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 "000.000". The trick of this calculation is to first calculate the interval between the current date and "1900-01-01", and then add it to "1900-01-01" to get a special date. This technique can be used to calculate many different dates. The next example also uses this technique to generate different dates from the current date. This week's Monday here I use the wk interval to calculate which day is this week's Monday.

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

The first day of the year now uses the interval of the year (yy) to show the first day of the year.

Select DATEADD (yy, DATEDIFF (yy,0,getdate ()), 0)

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

In the middle of the night of the day, you used to truncate the time part through the getdate () function in order 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 the midnight time.

Select DATEADD (dd, DATEDIFF (dd,0,getdate ()), 0)

As you can see by delving into the calculation of DATEDIFF and DATEADD functions, you can find many different dates that may be meaningful by using simple DATEDIFF and DATEADD functions. All the examples so far just calculate the number of intervals between the current time and "1900-01-01", and then add it to the "1900-01-01" interval to calculate the date. Suppose you change the number of intervals, or use different intervals to call the DATEADD function, or subtract the intervals instead of increasing them, then you can find and many different dates through these small adjustments. Here are four examples that use another DATEADD function to calculate the time interval before and after the last day to replace the DATEADD function. 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 the last day of the month. One thing to keep in mind is that in Sql Server the time 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 the time of the last moment of the day that Sql Server can record. The last day of last year

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

Select dateadd (ms,-3,DATEADD (yy, DATEDIFF (yy,0,getdate ()), 0))

The last day of this month now, in order to get the last day of this month, I need to modify the statement to get the last day of last month slightly. The modification requires adding 1 to the current date compared with the time interval returned by "1900-01-01" with DATEDIFF. By adding one month, I calculate the first day of the next month, and then subtract 3 milliseconds, thus calculating 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 (mmem0je getdate ()) + 1,0)

On the last day of the year, you should master this practice now. This is the script for calculating the last day of the year.

Select dateadd (ms,-3,DATEADD (yy, DATEDIFF (yy,0,getdate ()) + 1,0))

The first Monday of this month is all right, and now is the last example. Here I'm going to calculate the first Monday of the month. This is a calculation script.

Select DATEADD (wk, DATEDIFF (wk,0,dateadd (dd,6-datepart (day,getdate ()), getdate ()), 0)

In this example, I used the script "Monday of this week" and made a little modification. The modified part is to replace the "getdate ()" part of the original script with the 6th day of the month, and replace the current date with the 6th day of the month so that the calculation can get the first Monday of the month. Summary

By using this mathematical method of calculating the time interval of a date, I find it valuable to show a useful calendar for the interval between two dates. Note that this is just one way to calculate these dates. Keep in mind that there are many ways to get the same results. If you have other methods, that's good, 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.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.