SQL Server Date Calculation

Usually, you need to get the current date and calculate some other date, for example, your program may need to determine the first or last day of one months. Most of you probably know how to divide the date (year, month, day, etc.), and then just use the divided 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 figure out some of the different dates you might want to use in your program.

Before using the examples in this article, you must be aware of the following issues. Most probably not all examples of the results performed on different machines may not be the same, which is entirely determined by which day is the first day of one weeks of this setting. The first day (Datefirst) setting determines which day your system uses as the first day of the week. All of the following examples are created in Sunday as the first day of the week, that is, the first day is set to 7. If your first day is different, you may need to adjust these examples so that it matches the settings of the first day. You can check the first day setting by using 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 by adding or decreasing the time interval to obtain a new date. To learn more about the DateDiff and DATEADD functions and the time interval you can read Microsoft online Help.

Using the DateDiff and DATEADD functions to calculate dates is a little different from the way you would think to convert from the current date to the date you need. You have to think about it in terms of time interval. For example, how many intervals are there between the current date and the date you want to get, or how much time interval between today and a day (such as 1900-1-1), and so on. Understanding how to focus on time intervals can help you easily understand my different date calculation examples.

First day of one months

For the first example, I'll show you how to go to the last day of the month from the current date. 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 is calculated by calculating the time interval, and then adding and subtraction to get the date you want to calculate.

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

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)

Deep DATEDIFF and the DATEADD function Calculation

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

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

Summarize

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.

SQL Server Date Calculation