SQL time and date operations (time and date functions, time and date formats, time and date conversion parameters, time and date comparison, and time and date calculation)

To operate on time in SQL, You need to master four time functions:Dateadd, datediff, datepart, datename.

Dateadd is to add or subtract a time on a specified date.
For example, if you add a 3-day select dateadd (day, 3, '2017-05-06 ') on the specified date, the returned result is 2010-05-09.

Datediff is the date and time parameter that returns two specified dates.
For example, select dateadd (day, '2017-05-09 ', '2017-05-06'), the returned result is 3.

Datepart is the part that returns the specified date parameter of the specified date.
For example, if select datepart (day, '2017-05-06 ') is selected, 6 is returned.

Datename returns the string of the specified date parameter of the specified date. It is different from datepart, mainly in week and weekday.
For example, select datename (weekday, '2017-05-06 ') returns Thursday.
If the following result lists the week number of the specified date in the current year, the day is the day of the week:
Select the week of the current year = datename (Week, '1970-05-06 '), today is the day of the week = datename (weekday, '1970-05-06 ')

BelowCodeValue collection, about the time and date format, time and date conversion parameters:
Select convert (varchar, getdate (), 120)/* result: 17:16:28 */

Select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','') /* result: 20100506171628 */

Select convert (varchar (12), getdate (), 111)/* result: 2010/05/06 */

Select convert (varchar (12), getdate (), 112)/* result: 20100506 */

Select convert (varchar (12), getdate (), 102)/* result: 2010.05.06 */

Select convert (varchar (12), getdate (), 101)/* result: 05/06/2010 */

Select convert (varchar (12), getdate (), 103)/* result: 06/05/2010 */

Select convert (varchar (12), getdate (), 104)/* result: 06.05.2010 */

Select convert (varchar (12), getdate (), 105)/* result */

Select convert (varchar (12), getdate (), 106)/* result: 06 05 2010 */

Select convert (varchar (12), getdate (), 107)/* result: 05 06,201 0 */

Select convert (varchar (12), getdate (), 108)/* result: 17: 20: 09 */

Select convert (varchar (12), getdate (), 109)/* result: 05 6 2010 */

Select convert (varchar (12), getdate (), 110)/* result: 05-06-2010 */

Select convert (varchar (12), getdate (), 113)/* result: 06 05 2010 1 */

Select convert (varchar (12), getdate (), 114)/* result: 17: 19: 44: 123 */

Generally, a date with a time value greater than or equal to the same number can be used.
For example, select twrefundprice from twchangerefund where sdate <= getdate () and edate> = getdate ()

The following lists some classic instances to better understand the time and date operations in SQL:

Calculate the SQL script for the first day of a month: Select dateadd (mm, datediff (mm, 0, getdate (), 0)
Explanation: datediff (mm, 0, getdate () is the number of months between the current date and the date "1900-01-0100:00:00. 000. Remember: The period and time variables start from 00:00:00. 000, just like milliseconds.

Monday of the week: Select dateadd (wk, datediff (wk, 0, getdate (), 0)
first day of the year: Select dateadd (YY, datediff (YY, 0, getdate (), 0)
First day of the quarter: Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
midnight of the day: Select dateadd (DD, datediff (DD, 0, getdate (), 0)
last day of last month: Select dateadd (MS, -3, dateadd (mm, datediff (mm, 0, getdate (), 0)
last day of last year: Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0)
last day of the month: Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0)
last day of the year: Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0)
first Monday of the month: Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)