SQL Server time function usage detailed

The time function in SQL is very useful, especially when we are doing initial assignment, complex query, it is particularly convenient.

1. Get System Current Time

Select GETDATE ()

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

--Today is 2009-2-24--Tuesday

SELECT datename (year, GETDATE ()) as ' year Name '--------back: 2009

SELECT datename (month, GETDATE ()) as ' month Name '--------back: 02

SELECT Datename (Weekday, GETDATE ()) as ' weekday Name '------Back: Tuesday

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

Select DATEADD (mm,2, ' 2008-8-8 ')--------------back: 2008-10-08 00:00:00.000

Select DATEADD (dd,2, ' 2008-8-8 ')--------------back: 2008-08-10 00:00:00.000

Select DATEADD (Hh,-1,getdate ())--------------back: 2009-02-23 12:46:46.450, one hours before return

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 value equal to (date2-date1) in the date part.

SELECT DateDiff (hour, ' 4:00am ', ' 5:50am ')---------------------------back: 1

SELECT DateDiff (month, ' 1987/05/02 ', ' 1995/11/15 ')------------------back: 102

SELECT DateDiff (Day, ' 00:00 ', ' 23:59 ')------------------------------return: 0

SELECT DateDiff (Day, ' 1999/07/19 00:00 ', ' 1999/07/23 23:59 ')------return: 4

SELECT DateDiff (month, ' 1999/07/19 ', ' 1999/07/23 ')------------------back: 0

SELECT DateDiff (month, ' 1999/07/19 ', ' 1999/08/23 ')------------------back: 1

Instance: querying for updated data for the 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 '--------back: 2009

SELECT DATEPART (Month,getdate ()) as ' month '---------return: 2

SELECT DATEPART (Weekday,getdate ()) as ' weekday '---------return: 3, such as: Sunday = 1, Saturday = 7

SELECT Day (getdate ())-----------------------return: 24

Note: day, MONTH, and year functions are synonyms for DATEPART (DD, date), DATEPART (mm, date), and DATEPART (yy, date) respectively.

Appendix: DatePart