Add and subtract functions for SQL Server Date: DATEDIFF DATEADD

Original address: http://blog.csdn.net/xyzqiang/article/details/6577831

Add and subtract functions for SQL Server Date: DATEDIFF DATEADD

DATEDIFF: Returns the number of date boundaries and time boundaries across two specified dates, syntax: DATEDIFF (datepart, StartDate, enddate) minus EndDate with StartDate
Note: datepart specifies which part of the date should be calculated for the difference of the parameter, whose date is subtracted, only the boundary value is concerned, example select DATEDIFF (year, ' 2008-12-31 ', ' 2009-1-1 ') returns 1

DATEADD: Returns a new datetime value that is added to the specified date after a time interval. Syntax: DATEADD (datepart, number, date)
Note: datepart specifies the part of the date to return the new value
Number used to increase the value of the datepart. A positive number indicates an increase, a negative number is reduced, and if it is a decimal, the fractional part is ignored and no rounding is done.

Calculated by some dates of DATEDIFF and DATEADD

1) First day of the year
SELECT DATEADD (Year,datediff (Year,0,getdate ()), 0)
Note: First DateDiff (Year,0,getdate ())-Calculates the number of years that the current year differs from 1900, and then the first day of the year by calculating the date of 1900-1-1 plus the number of years of difference

2) The first day of a season
SELECT DATEADD (Quarter,datediff (Quarter,0,getdate ()), 0)
Note: First DateDiff (Quarter,0,getdate ())-Calculates the number of quarters between the current month and 1900, then the first day of the season by calculating the date of 1900-1-1 plus the quarter

3) First day of one months
SELECT DATEADD (Month,datediff (Month,0,getdate ()), 0)
Note: First DateDiff (Month,0,getdate ())-Calculates the number of months that the current month differs from 1900, and then the first day of the month by calculating the date of the month in 1900-1-1 plus the difference.

4) The first day of the week
SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

5) Midnight of the Day (00:00:00.000)
SELECT DATEADD (Day,datediff (Day,0,getdate ()), 0)

6) Last day of the month
SELECT DATEADD (Ms,-3,dateadd (Month,datediff (Month,0,getdate ()), 0))
Note: Subtract 3 milliseconds from the first day of the month, which is the most one day of the month. The time for the SQL SERVER datetime type is accurate to 3 milliseconds.

7) Last day of the month
SELECT DATEADD (Ms,-3,dateadd (Month,datediff (Month,0,getdate ()) +1,0)

8) Number of days of the month
i) SELECT Day (DATEADD (Ms,-3,dateadd (Month,datediff (Month,0,getdate ()) +1,0)))
II) SELECT 32-day (GETDATE () + (32-day (GETDATE ())))

9) Last day of the year
SELECT DATEADD (Ms,-3,dateadd (Year,datediff (Year,0,getdate ()) +1,0)

10) The first day of the week
SELECT DATEADD (Day,1-datepart (Weekday,getdate ()), GETDATE ())
The last day of the week
SELECT DATEADD (Day,7-datepart (Weekday,getdate ()), GETDATE ())
SELECT DATEADD (Weekday,datediff (Weekday,0,dateadd (Day,6-datepart (Day,getdate ()), GETDATE ()), 0)

5th-Period conversion function convert CAST
The Style in CONVERT. Parameters: 108 and 114 can only get time.
Example SELECT CONVERT (NVARCHAR (), GETDATE (), 108)---12:41:15
SELECT CONVERT (NVARCHAR, GETDATE (), 12:43:12:590)---

6th Judgment Function ISDATE () determines whether an input expression is a valid date. Returns 0 if valid returns 1, and the return value is INT.

Add and subtract functions for SQL Server Date: DATEDIFF DATEADD