Sqlserver:datepart, Dateadd,datediff,datename function

SELECT DATEDIFF (year, ' 1992-11-28 ', GETDATE ())--age, where year can be replaced by day MONTH

SELECT DATEPART (Month,getdate ())-Returns an integer representing the specified date portion of the specified date. such as 12

SELECT datename (WEEKDAY, ' 2014-11-12 ')--Returns the specified day of the week, such as Monday,

SELECT Datename (Weekday,getdate ())--Returns the current date is the day of the week

SELECT Datename (Week,getdate ())--Returns the current number of weeks of the year, such as 51

Example:

--Query the name and age of all boys older than 20 years in the Student table use Teachinggoselect Student.sname,datediff (Year,birthday,getdate ()) as ' age ' from Studentwhere student.sex= ' man ' and DATEDIFF (Year,birthday,getdate ()) >20

1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value based on adding a period of time to the specified date
Example: Add 2 days to the date
Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000

3. DateDiff returns the number of date and time boundaries across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

4. DatePart returns an integer representing the specified date portion of the specified date.
SELECT DATEPART (month, ' 2004-10-15 ')--return 10

5. Datename returns a string representing the specified date part of the specified date
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday

Sqlserver:datepart, Dateadd,datediff,datename function