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)