SQL Server datediff and dateadd

1,Datediff

 

Syntax description:

Datediff (date-part, date-expression1, date-expression2)

Function:

Returns the interval between two dates.

Parameter description:

Date-part:

Year | quarter | month | week | Day | hour | minute | second | millisecond

 

Date-part specifies the date part of the interval to be calculated.

The start date of an interval in the date-expression1. Subtract this value from the date-expression2 and return the value of date-parts between two parameters.

The end date of an interval in the date-expression2. Subtract the Date-expression1 from this value and return the value of date-parts between two parameters.

 

2,Dateadd

 

Syntax
Dateadd (datepart, number, date)
The dateadd () function adds or removes the specified time interval in the date.
The date parameter is a valid date expression.
Number is the number of intervals you want to add. For the future time, this number is a positive number, and for the past time, this number is a negative number.
Datepart
Parameters can be the following values:Example: 1) Select dateadd (DD, 2, getdate () add two days based on the current date 2) Calculate the first day of a month select dateadd (mm, datediff (mm, 0, getdate (), 0) Remarks: The datediff (mm, 0, getdate () function calculates the current date and "00:00:00. 000 "the number of months between this date. Remember: The period and time variables start from "00:00:00. 000", just like milliseconds. This is why you can specify the first time expression as "0" in the datediff function ". 3) on Monday of this week, select dateadd (wk, datediff (wk, 0, getdate (), 0) 4) Select dateadd (YY, datediff (YY, 0, getdate (), 0)
3. query the person who has a birthday in a specified date segment. SQL-- Test Data declare @ t table (ID int, name varchar (10), birthday datetime) insert @ t select 1, 'A', '2017-01-01 'Union all select 2, 'bb ', '2017-02-29' Union all select 3, 'bb ', '2017-03-01' Union all select 4, 'bb ', '2014-04-01 'Union all select 5, 'bb', '2014-05-01 'Union all select 6, 'bb', '2014-11-21 'Union all select 7, 'bb ', '2017-12-11' declare @ dt1 datetime, @ dt2 datetime -- query the records from 1989 to 2003. Select @ dt1 = '2017-12-05 ', @ dt2 = '1970-02-28 'select * From @ twhere dateadd (year, datediff (year, birthday, @ dt1), birthday) between @ dt1 and @ dt2 or dateadd (year, datediff (year, birthday, @ dt2), birthday) between @ dt1 and @ dt2/* -- result ID name birthday ---------------- -------------- -------------------------- 1 aa 00:00:00. 0007 BB 00:00:00. 000 -- */-- query the set @ dt2 = '2017-02-28 'select * From @ twhere dateadd (year, datediff (year, birthday, @ dt1) records from ), birthday) between @ dt1 and @ dt2 or dateadd (year, datediff (year, birthday, @ dt2), birthday) between @ dt1 and @ dt2/* -- Query Result ID name birthday ------------------------------- -------------------------- 1 aa 00:00:00. 0002 BB 00:00:00. 0007 BB 00:00:00. 000 --*/