ASP and SQL Server time processing methods

Server|sql|sqlserver

In the development of database applications, often encounter problems of processing time, such as query the specified time records. Here are some common questions to combine with some of your own experience and discuss these issues with you.

first introduces the use of several main functions of processing time in SQL Server:


   


getdate () function: Gets the current date and time of the system. Returns the value of the datetime type.


usage: getdate ()


Example:


Select GETDATE () as Dte,dateadd (Day,-1,getdate ()) as Nowdat


Output Result:


DTE Nowdat


  --------------------------- ---------------------------


1999-11-21 19:13:10.083 1999-11-20 19:13:10.083


  


(1 row (s) affected)


  


datepart () function: Returns the specified portion of a time as an integer.


usage: DatePart (datepart,date)


parameter description: DatePart to return the part of the time, commonly used to value year, month, day, hour, minute.


date is the specified time.


Example:


SELECT DATEPART (month, GETDATE ()) as ' month number '


Output Result:


Month Number


  ------------


11


  


(1 row (s) affected)


  


DateAdd () function: Returns a new time value by adding an integer value to the specified part of the specified time.


usage: DATEADD (datepart,number,date)


parameter Description: DatePart (IBID.)


Date (IBID.)


number to increase the value of the integer, can be positive negative, positive values return the time after the date value, negative returns the date

The time value before



Example:


Select GETDATE () as Today


Select DATEADD (day,-1,getdate ())


Select DATEADD (day,1,getdate ())


Output:


Today


  ---------------------------


1999-11-21 19:42:41.410


  


(1 row (s) affected)


  


Yesterday


  ---------------------------


1999-11-20 19:42:41.410


  


(1 row (s) affected)


  


Tomorrow


  ---------------------------


1999-11-22 19:42:41.410


  


(1 row (s) affected)


  


DateDiff () function: Returns the difference that is computed two times in the specified time section. Returns an integer value. such as between 1991-6-12 and 1991-6-21 with days


to calculate the difference between 9 days, 1998-6-12 and 1999-6-23 by the year is 1 years, 1999-12-1 and 1999-3-12 by the month difference 9 months


usage: DateDiff (darepart,date1,date2)


parameter Description: DatePart (IBID.)


Date1, Date2 (ditto date)


Example:


Select DateDiff (month, ' 1991-6-12 ', ' 1992-6-21 ') as a


Output:


a


  -----------


12


  


(1 row (s) affected)