In developing database applications, problems with processing time are often encountered, such as recording of the specified time, etc. Below these common questions, combine some of your own experiences, and explore this kind of problem.
First introduce, the usage of several major functions in SQL Server processing:
GetDate () function: obtain the current date and time of the system. The return value is a 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 the time in an integer.
Usage: DatePart (DatePart, Date)
Parameter Description: The time of the time returned when DatePart, commonly used value Year, Month, Day, Hour, Minute.
Date is the time specified.
example:
Select datepart (Month, getdate ()) AS 'MONTH NUMBER'
Output results:
MONTH NUMBER
11
(1 row (s) affected)
DATEADD () function: A new time value is returned by adding an integer value by adding an integer value to the specified time of the specified time.
Usage: dateadd (DatePart, Number, Date)
Parameter description: DatePart (ibid)
Date (same)
Number To increase the value, integer, can be negative, positive value returns the time value after DATE, and the negative value 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 between two times to specify a time portion. Returns an integer value. Such as between 1991-6-12 and 1991-6-21
It is necessary to differ 9 days, 1998-6-12 and 1999-6-23 are different from the year, and the 1999-12-1 and 1999-3-12 are considered 9 months as monthly basis.
Usage: Datediff (Darepart, Date1, Date2)
Parameter description: DatePart (ibid)
Date1, Date2 (same as date)
example:
SELECT dateDiff (Month, '1991-6-12', '1992-6-21') AS A
Output:
a
12
(1 row (s) affected)
