SQL Server time function usage

The time functions in SQL are very useful, especially when we perform initial assignment and complex queries.

1. Obtain the current system time

Select getdate ()

2. datepart (date) returns the string representing the specified date part of the specified date.

-- Today is -- Tuesday

Select datename (year, getdate () AS 'year name' -------- return: 2009

Select datename (month, getdate () AS 'month name' -------- return: 02

Select datename (weekday, getdate () AS 'weekday name' ------ return: Tuesday

3. DATEADD (datepart, number, date) returns a new datetime value based on a period of time added to the specified date.

Select DateAdd (MM, 2, '2017-8-8 ') ------------ return: 2008 00:00:00. 000

Select DateAdd (dd, 2, '2017-8-8 ') ------------ returned: 2008 00:00:00. 000

Select dateadd (hh,-1, getdate () -------------- return: 12:46:46. 450, return the time of the previous hour

4. DATEDIFF (date-part, date-expression-1, date-expression-2) returns the interval between two dates.

This function calculates the number of date parts between two specified dates. The result is a signed integer equal to (date2-date1) in the date section.

SELECT datediff (hour, '4: 00am', '5: 50am') ------------------------- return: 1

SELECT datediff (month, '2014/1/02', '2014/3/15') -------------- return: 1987/05

SELECT datediff (day, '00: 00', '23: 59') ---------------------------- return: 0

SELECT datediff (day, '2014/1/19 ', '2014/3/23') ------ return: 4

SELECT datediff (month, '2014/1/19', '2014/3/23') -------------- return: 0

SELECT datediff (month, '2014/1/19', '2014/3/23') -------------- return: 1

Instance: query the updated data on the current day.

Select * from tableName where datediff (dd, F_EditTime, getdate () = 0

5. DATEPART (datepart, date) returns an integer representing the specified date part of the specified date.

-- Today is 2009-2-24 Tuesday

Select datepart (year, getdate () as 'Year' -------- return: 2009

Select datepart (month, getdate () as 'month' --------- return: 2

Select datepart (weekday, getdate () as 'weekday' --------- return: 3, for example, Sunday = 1, Saturday = 7

Select day (getdate () ----------------------- return value: 24

Note: The DAY, MONTH, and YEAR functions are synonyms of DATEPART (dd, date), DATEPART (mm, date), and DATEPART (yy, date.

Appendix: datepart