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.

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 the time in SQL 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 the time that a SQL 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

I hope these examples will give you a little bit of 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 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))