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.
Let's begin by introducing 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 results:
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 results:
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 date
Previous time value
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 calculated in the specified time portion of two time. Returns an integer value. such as between 1991-6-12 and 1991-6-21 with the day
To calculate the difference between 9 days, 1998-6-12 and 1999-6-23 by year, the difference is 1 years, 1999-12-1 and 1999-3-12 monthly difference of 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)
