T-SQL Time operations (DATEADD, DATEDIFF, datepart, datename)

Tag:sql    database    

--1. Current system date, time select GETDATE ()--2015-01-06 09:27:27.277--2. The time operation DateAdd returns a new datetime value DATEADD (datepart, on the basis of adding a period of time to a specified date) 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) | The millisecond (MS) Date parameter is a valid date 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. Example: Select GETDATE () Select DATEADD (Year,1,getdate ())--2016-01-06 09:27:27.277 Select DATEADD (month,1,g Etdate ())--2017-02-06 09:27:27.277 Select DATEADD (day,1,getdate ())--2015-01-07 09:27:27.277select DateAdd (week,1, GETDATE ())--2015-01-13 09:27:27.277select DateAdd (Weekday,1,getdate ())--2015-01-07 09:27:27.277select DateAdd (hour , 1,getdate ())--2015-01-06 10:27:27.277select DateAdd (Minute,1,getdate ())--2015-01-06 09:28:27.277select 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) | The millisecond (ms) StartDate and EndDate parameters are valid date expressions. return value: (EndDate)-(StartDate) Example: Select DateDiff (Year,getdate (), DateAdd (Year,2,getdate ()))--Return: 2 select * FROM Crm_contra CT 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 need, you need to speak the date time format Select DATEPART (year, GETDATE ()) As ' Years ' Select DATEPART (month, GETDATE ()) as ' month ' Selec T 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 portion of the specified date select Datename (Weekday, GETDATE ()) as ' What day is today? ' 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 week Monday select DATEADD (wk, DATEDIFF (Wk,0,getdate ()),-7)--Last week 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 week 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 ()) + 1, 0))--. this week Monday select DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)--. this week Sunday select DATEADD (Wk,datediff ( Wk,0,getdate ()), 6)--. the 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)  --. Day of the Night 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 (y Y, DATEDIFF (Yy,0,getdate ()), 0))--. this month's first Monday select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day , GETDATE ()), GETDATE ()), 0)

T-SQL Time operations (DATEADD, DATEDIFF, datepart, datename)