Comparison of SQL Time function collation all _mssql

1. Current system date, time
Select GETDATE ()
2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date
For example: Add 2 days to date
Select DATEADD (day,2, ' 2004-10-15 ')--back: 2004-10-17 00:00:00.000

For example: Query the contents of the last three days of the current time descending order
SELECT * FROM table where time between DateAdd (Day,-3,getdate ()) and getdate () Order BY c_id Desc

3. DateDiff returns the number of date and time boundaries across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17
4. DatePart returns an integer representing the specified date part of the specified date.
SELECT DATEPART (month, ' 2004-10-15 ')--return 10
5. Datename returns a string representing the specified date part of the specified date
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday
6. Day (), month (), year ()--can be compared with datepart
Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), 114)
Select Datename (DW, ' 2004-10-15 ')
Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is the week =datename (weekday, ' 2004-10-15 ')
function parameters/Functions
GetDate () returns the current date and time of the system
DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates, as specified by interval date2-date1
DATEADD (interval,number,date) in the manner specified by interval, plus the date after number
DatePart (interval,date) returns the integer value of the specified part of the date, interval
Datename (Interval,date) returns the string name of the specified part of the date, interval
The set values for the parameter interval are as follows:
value abbreviation (SQL Server) Access and ASP description
Year Yy yyyy 1753 ~ 9999
Quarter Qq Q Season 1 ~ 4
Month Mm M Month 1 ~ 12
Day of the year Dy y, number of days of the year, 1-366
Day Dd D, 1-31
Weekday Dw W number of days in a week, day of the week 1-7
Week Wk WW Week, the first weeks of the year 0 ~ 51
Hour Hh H 0 ~ 23
Minute Mi N min 0 ~ 59
Second Ss S SEC 0 ~ 59
Millisecond ms-milliseconds 0 ~ 999
Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also available for access and ASP, and these functions are similar in usage
Example:
1.GetDate () for SQL Server:select GetDate ()
2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days
3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 i.e. Monday (Sunday is 1, Saturday is 7)
DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year
DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 i.e. 2005
The SQL Server DATEPART () function returns part of SQL Server datetime field.
The syntax for the SQL Server DATEPART () function is:
DATEPART (Portion, datetime)
where datetime is a SQL Server datetime field and part of the name is one of the following: Ms for milliseconds
Yy for year
Qq for quarter of the year
Mm for Month
Dy for the "the Year"
Dd for day of the Month
Wk for Week
Dw for the day of the Week
Hh for Hour
Mi for Minute
Ss for Second
Detailed Description:
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 DateDiff and DATEADD functions and time intervals 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 case, I used the "This week's Monday"