SQL Server time function usage (ii)

--1. Current system date, time
Select GETDATE ()--2015-01-06 09:27:27.277

--2. Time operation DateAdd Returns a new datetime value based on adding a period of time to a specified date
DATEADD (Datepart,number,date)
Note:
Datepart:year (yy, yyyy) | Quarter (QQ,Q) | Month (mm,m) | Week (WW,WK) | Day (Dd,dy) | Hour (h) | Minute (Mi,n) |
Second (ss,s) | Millisecond (ms)
The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative.
Cases:
Select GETDATE ()
Select DATEADD (Year,1,getdate ())--2016-01-06 09:27:27.277
Select DATEADD (Month,1,getdate ())--2017-02-06 09:27:27.277
Select DATEADD (Day,1,getdate ())--2015-01-07 09:27:27.277
Select DATEADD (Week,1,getdate ())--2015-01-13 09:27:27.277
Select DATEADD (Weekday,1,getdate ())--2015-01-07 09:27:27.277
Select DATEADD (Hour,1,getdate ())--2015-01-06 10:27:27.277
Select DATEADD (Minute,1,getdate ())--2015-01-06 09:28:27.277
Select DATEADD (Second,1,getdate ())--2015-01-06 09:28:28.277

--3. DateDiff returns the number of date and time boundaries across two specified dates.
DateDiff (Date-part, StartDate, EndDate)
Note:
Datepart:year (yy, yyyy) | Quarter (QQ,Q) | Month (mm,m) | Week (WW,WK) | Day (Dd,dy) | Hour (h) | Minute (Mi,n) |
Second (ss,s) | Millisecond (ms)
The StartDate and EndDate parameters are valid date expressions.
return value: (EndDate)-(StartDate)
Cases:
Select DateDiff (Year,getdate (), DateAdd (Year,2,getdate ()))--Return: 2
SELECT * from Crm_contract where DateDiff (Month,subtime,getdate ()) =0--View crm_contract application time Subtime is this month's data

--4. The DatePart function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.
--If you need to get the date you want, you need to say date time format
SELECT DATEPART (year, GETDATE ()) as ' years '
SELECT DATEPART (month, GETDATE ()) as ' month '
SELECT DATEPART (Day, GETDATE ()) as ' date '
SELECT DATEPART (week, GETDATE ()) as ' number of weeks of the year '
SELECT (DATEPART (Weekday, GETDATE ())-1) as ' Today is the week? '
SELECT DATEPART (Hour, GETDATE ()) as ' hour '
SELECT DATEPART (Minute, GETDATE ()) as ' min '
SELECT DATEPART (Second, GETDATE ()) as ' seconds '

--5. Datename returns a string representing the specified date part of a specified date
SELECT Datename (Weekday, GETDATE ()) as ' Today is the day of the week? '
SELECT datename (month,convert (varchar), GETDATE (), 126) as ' What month is it today? ‘
SELECT datename (day,convert (varchar), GETDATE (), 126) as ' What's the date today? ‘
SELECT datename (year,convert (varchar), GETDATE (), 126) as ' What year is this year? ‘

--Last Monday
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()),-7)
--Last Sunday
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()),-1)
--. this week Monday
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)
--. this week Sunday
Select DATEADD (Wk,datediff (Wk,0,getdate ()), 6)
--Next Monday
Select DATEADD (Wk,datediff (Wk,0,getdate ()), 7)
--Next Sunday
Select DATEADD (Wk,datediff (Wk,0,getdate ()), 13)
--1. First day of the month
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)

--. last day of the month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))
--. this Monday
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

--. this Sunday
Select DATEADD (Wk,datediff (Wk,0,getdate ()), 6)

--. first day of the year
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

--. the last day of the year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))
--. the first day of the quarter
SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

--. the night of the day
SELECT DATEADD (DD, DATEDIFF (Dd,0,getdate ()), 0)

--. last day of last month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

--. last day of last year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))


--. the first Monday of the month
Select DATEADD (wk,
DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)

SQL Server time function usage (ii)