Time functions in sqlserver

The time functions in SQL are very useful, especially when we perform initial assignment and complex queries. 1. Obtain the current system time selectgetdate () 2. DateName (datepart, date) returns the string representing the specified date part of the specified date. -- Today is -- Tuesday SELECTDATENAME (ye

The time functions in SQL are very useful, especially when we perform initial assignment and complex queries. 1. Obtain the system's current time select getdate () 2. DateName (datepart, date) returns the string representing the specified date part of the specified date. -- Today is -- select datename (ye

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. DateName(Datepart, date) returns a string that represents 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 after a period of time is 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 that represents the specified date of a 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 functions of DAY, MONTH, and YEAR are DATEPART (Dd,Date), DATEPART (Mm,Date), And DATEPART (Yy,Date.

Appendix: datepart

Date	Abbreviations
Year	Yy, yyyy
Quarter	Qq, q
Month	Mm, m
Dayofyear	Dy, y
Day	Dd, d
Week	Wk, ww
Hour	Hh
Minute	Mi, n
Second	Ss, s
Millisecond	MS