SQL-dateadd () and datediff ()

/*************************************** **********
** Author: wanzegui
** Time: 2009-02-03
** Reason: organize and collect data on the Internet to facilitate learning.
**************************************** **********/
/*
Convert (varchar (10), field name, conversion format)
Note:
This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, Char, varchar)
It is used only when they are converted to each other.
*/
/*
Datadiff (interval parameter, start date, end date)
Interval parameters: year, month, week, day, quarter, etc.
*/
/*
Dateadd (add parameter, interval, date)
Dateadd (year, 2,2008-01-01 ')
Add 2 years to the Year
Result: 2010-01-01
Dateadd (mm, 3,2008-01-01)
Add January 1, to the month of January 1 ,.
Result: 2008-03-01
*/
Select datediff (year, 0, getdate () -- year different from the 1900-01-01 date!
Select dateadd (wk, datediff (wk, 0, PMC requires completion date), 0) as Monday from mimierp .. v_report_orderbyall where PMC requires completion date is not null
Select datediff (mm, 0, getdate () -- returns the number of months between the return value and 0 ().
Select dateadd (mm, datediff (mm, 0, '2017-03-5 '), '2017-1-31') -- return the last day of the current month
Select dateadd (mm, datediff (mm, 0, '2017-03-5 '), '2017-1-1') -- returns the first day of the current month
Select datediff (wk, 0, getdate ())
Select dateadd (wk, datediff (wk, 0, getdate (), 0) -- // returns the first day of the current week of the current date-Monday
Select dateadd (wk, datediff (wk, 0, getdate (), 6) -- returns the weekend of the current week of the current date-Sunday
/*
The first day of a year

Now we use the interval of year (yy) to display the first day of the year.
*/
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
/*
First day of the quarter

If you want to calculate the first day of the quarter, this example shows you how to do it.
*/

Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
/*
Midnight of that day

In the past, the getdate () function was used to cut off the time part in order to return the time value. It will take into account whether the current date is in the middle of the night. In this example, the datediff and dateadd functions are used to obtain the midnight time point.
*/
Select dateadd (DD, datediff (DD, 0, getdate (), 0) -- returns the early morning

Select dateadd (QQ, datediff (QQ, 0, getdate (), 0) ---- return the first day of the quarter

Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0) -- returns the last day of the previous year, obtained by reducing 3 ms milliseconds
Select dateadd (YY, datediff (YY, 0, getdate (),-1) -- returns the last day of the previous year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0) -- get the last day of the year, get by 3 ms milliseconds
Select dateadd (YY, datediff (YY, 0, getdate () + 1,-1) -- return the last day of the previous year, get the first day of next year, and then subtract one day to get

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/Johnson_hejun/archive/2009/02/19/3909957.aspx