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 |