SQL Server various Date calculation methods

Usually, you need to get the current date and calculate some other date, for example, your program may need to judge the first or last day of one months. Most of you probably know how to split dates (year, month, day, etc.), and then just use the split year, month, day, etc in several 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 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 the one week 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 established in Sunday as the first day of the week, with the first day set to 7. If your first day setting is not the same, you may need to adjust these examples so that it matches the different first-day settings. You can check the first day setting by using the @ @DATEFIRST function.

To understand these examples, 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 the Datedi FF and DATEADD functions and the time interval 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.