Date and time functions in SQL Server

Date and time functions in SQL Server:

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 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

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/Functions

GetDate () returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates, as specified by interval date2-date1 DATEADD (interval,number,date) in the manner specified by interval, plus the date after number DatePart (interval,date) returns the integer value of the specified part of the date, interval Datename (Interval,date) returns the string name of the specified part of the date, interval

The set values for the parameter interval are as follows:

Value abbreviations (SQL Server) (Access and ASP) description Year Yy yyyy 1753 ~ 9999 Quarter Qq Q Season 1 ~ 4 Month Mm M Month 1 ~ 12 Day of the year Dy y, number of days of the year, 1-366 Day Dd D, 1-31 Weekday Dw W number of days in a week, day of the week 1-7 Week Wk WW Week, the first weeks of the year 0 ~ 51 Hour Hh H 0 ~ 23 Minute Mi N min 0 ~ 59 Second Ss S SEC 0 ~ 59 Millisecond ms-milliseconds 0 ~ 999

Access and ASP Use Date () and now () to obtain the system datetime, where Datediff,dateadd,datepart is also used in 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

　　please contact the site, timely note your name. Contact Email: edu#chinaz.com (change # to @).