SQL Server Date-time functions

1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date
For example: Add 2 days to date
Select DATEADD (day,2, ' 2004-10-15 ')--back: 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 part 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

6. Day (), month (), year ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is the week =datename (weekday, ' 2004-10-15 ')

function	parameters/Features
getdate ( )	> return line The current date and time of the EC
datediff  (interval,date1,date2)	Returns the difference between date2  and date1 two dates, as specified by interval   date2-date1
dateadd  (interval,number,date)
datepart  (interval,date)	Return date, interval the integer value of the specified section
datename  (interval,date)	returns date, interval the string name corresponding to the specified section

The set values for the parameter interval are as follows:

Value	Abbreviation (SQL Server)	(Access and ASP)	Description
Year	Yy	yyyy	Year 1753 ~ 9999
Quarter	Qq	Q	Quarter 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of the Year	Dy	Y	The number of days in a year, the first day of the year 1-366
Day	Dd	D	Day, 1-31
Weekday	Dw	W	The number of days in a week, the day ordinal of a week, 1-7
Week	Wk	Ww	Weeks, the first weeks of the year 0 ~ 51
Hour	Hh	H	Time 0 ~ 23
Minute	Mi	N	Minutes 0 ~ 59
Second	Ss	S	seconds 0 ~ 59
Millisecond	Ms	-	Millisecond 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also available for access and ASP, and these functions are similar in usage

Example:
1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 i.e. Monday (Sunday is 1, Saturday is 7)
DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year
DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 i.e. 2005