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 perform time operations in SQL, You need to master four time functions: dateadd, datediff, datepart, and 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 ')

For more information about time and date formats and time and date conversion parameters, see the following code values:
Select CONVERT (varchar, getdate (), 120)/* result: 17:16:28 */

Select 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 this week: select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0)
First day of a 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 ))
The first Monday of this month: select DATEADD (wk, DATEDIFF (wk, 0, dateadd (dd, 6-datepart (day, getdate (), getdate (), 0)

This article is from the "java Technology Learning" blog. For more information, contact the author!