SQL time function Summary (2)

You can understand that by using simple DATEDIFF and DATEADD functions, you can find many different dates that may be meaningful.
All the examples so far only calculate the number of time intervals between the current time and "", and then add it

The date is calculated based on the time interval of "1900-01-01. Assume that you modify the number of time intervals or use different time intervals to call

The DATEADD function, or subtract the time interval instead of increasing, then you can find and have different dates through these small adjustments.

Here are four examples of using another DATEADD function to calculate the last day to replace the two time intervals before and after the DATEADD function.

Last day of last month

This is an example of calculating the last day of the last month. It is obtained by subtracting 3 ms from the last day of the month. One thing

Remember that in SQL Server, the time is accurate to 3 ms. That's why I need 3 milliseconds to get the date and time I want.

SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))
The time part of the calculated date contains the time of the last day ("23: 59: 59: 997") that an SQL Server can record.

.

Last day of last year

In the above example, to get the last day of last year, you need to subtract 3 ms from the first day of this year.

SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))

Last day of this month

Now, in order to get the last day of this month, I need to slightly modify the statements to get the last day of the previous month. To be modified

DATEDIFF compares the current date with the time interval returned by "1900-01-01" and Adds 1. By adding a month, I calculate the first day of the next month, and then

After 3 milliseconds, the last day of the month is calculated. This is the SQL script used to calculate the last day of the month.

SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))

Last day of this year

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

SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0 )).

The first Monday of this month

Now is the last example. Here I want to calculate the first Monday of this month. This is the computing script.

Select DATEADD (wk, DATEDIFF (wk, 0,

Dateadd (dd, 6-datepart (day, getdate (), getdate (), 0)

In this example, I used the "Monday of this week" script and made some modifications. The modified part is

The "getdate ()" part is replaced with the calculation of the first day of the month. In the calculation, the current date is replaced with the 6th day of the month so that the calculation can obtain the month.

The first Monday.

Summary

I hope these examples will give you some inspiration when you use the DATEADD and DATEDIFF functions to calculate the date. By using this calculation date

The mathematical method of time interval, I found that it is valuable to display useful calendars between two dates. Note that this is only to calculate these dates

. Remember, there are still many ways to get the same computing results. If you have other methods, it would be nice. If you don't,

I hope these examples will give you some inspiration when you use the DATEADD and DATEDIFF functions to calculate the dates that your program may use.

---------------------------------------------------------------
Appendix, other date Handling Methods

1) Remove the hour, minute, and second
Declare @ datetime
Set @ = getdate () -- '2017-7-1 10:00:00'
SELECT @, DATEADD (day, DATEDIFF (day, 0, @), 0)

2) display the day of the week
Select datename (weekday, getdate ())

3) how to obtain the number of days in a month
Declare @ m int
Set @ m = 2 -- month
Select datediff (day, '2017-'+ cast (@ m as varchar) +'-15', '2017-'+ cast (@ m + 1 as varchar) + '-

15 ')

In addition, get the number of days this month
Select datediff (day, cast (month (GetDate () as varchar) + '-' + cast (month (GetDate ()

Varchar) + '-15', cast (month (GetDate () as varchar) +'-'+ cast (month (GetDate () + 1 as varchar)

+ '-15 ')
Or use the script to calculate the last DAY of the month, and then use the DAY function area for the last DAY.
SELECT Day (dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 )))

4) determine whether a leap year is used:
SELECT case day (dateadd (mm, 2, dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))))

When 28 then 'Year' else 'Year' end
Or
Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate

() + '-02-01 '))
When 28 then 'Year' else 'Year' end

5) How many days of 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

SQL date operations
Dump transaction database with no_log

Select datename (weekday, getdate () to obtain the number of weeks of the current day
Select convert (char (10), getdate (), 21)

Cast (@ Today + ''+ @ DutyTime_1 as datetime) string combination generation time type

If exists (select 1 from master .. sysdatabases where name = 'example ') to determine whether a record exists

Cast (ltrim (str (@ thisyear, 4) + '-' + ltrim (str (@ thismonth, 2) + '-' + '21 00:00:00 'as datetime)
1. The first day of a month
Select DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0)

2. Monday of the week
Select DATEADD (wk, DATEDIFF (wk, 0, getdate (), 0)

3. The first day of the year
Select DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0)

4. The first day of the quarter
Select DATEADD (qq, DATEDIFF (qq, 0, getdate (), 0)

5. midnight of the day
Select DATEADD (dd, DATEDIFF (dd, 0, getdate (), 0)

6. Last day of last month
Select dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))

7. Last day of last year
Select dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))

8. Last day of the month
Select dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))

9. The last day of the year
Select dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0 ))

10. the first Monday of this month
Select DATEADD (wk, DATEDIFF (wk, 0, dateadd (dd, 6-datepart (day, getdate (), getdate (), 0)

Returns the current date and time.
By using the GETDATE () function, you can obtain the current date and time. The GETDATE () function can be used as the default value of a DATEDIME field. This is useful for saving the time when a record is inserted. To create a table with records containing the current date and time, you can add a DATETIME field and specify its default value as the return value of the function GETDATE (), as shown in the following figure:

Create TABLE site_log (
Username VARCHAR (40 ),
Useractivity VARCHAR (100 ),
Entrydate datetime default getdate ())

Conversion date and time
The Return Value of the GETDATE () function is only displayed in seconds. In fact, the internal time of the SQL Server can be accurate to milliseconds (to be exact, it can be accurate to 3.33 milliseconds ).
To get the date and time in different formats, you need to use the CONVERT () function (). For example, when the current statement is executed, the display time includes milliseconds:

Select CONVERT (VARCHAR (30), GETDATE (), 9)

Note the use of Number 9 in the example. This number specifies the date and time format used for displaying the date and time. When this statement is executed, the following date and time are displayed:
Nov 30 1997 3: 29: 55: 170AM
(1 row (s) affected)