T-SQL time operations (dateadd, datediff, datepart, datename), datepartdatename

T-SQL time operations (dateadd, datediff, datepart, datename), datepartdatename

-- 1. current System Date and Time select getdate () -- 09:27:27. 277--2. the Time Operation dateadd returns the new datetime value dateadd (datepart, number, date) based on a period of time added to the specified 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) 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. Example: select getdate () select dateadd (year, 1, getdate () -- 09:27:27. 277 select dateadd (month, 1, getdate () -- 09:27:27. 277 select dateadd (day, 1, getdate () -- 09:27:27. 277 select dateadd (week, 1, getdate () -- 09:27:27. 277 select dateadd (weekday, 1, getdate () -- 09:27:27. 277 select dateadd (hour, 1, getdate () -- 10:27:27. 277 select dateadd (Minute, 1, getda Te () -- 09:28:27. 277 select dateadd (Second, 1, getdate () -- 09:28:28. 277--3. datediff returns the number of dates and time boundaries that span 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) Example: select datediff (year, getdate (), dateadd (year, 2, getdate () -- return: 2 SELECT * FROM crm_contract where datediff (month, Subtime, getdate () = 0 -- view the crm_contract application time subtime is the data of this month -- 4. the datepart function is used to return a separate part of the date/time, such as year, month, day, hour, and minute. -- If you need to get the date you need, you need to format the select datepart (year, getdate () as 'Year' select datepart (month, getdate ()) as 'month' select datepart (day, getdate () as 'date' select datepart (week, getdate () as 'Week Number of the current year' SELECT (DATEPART (weekday, getdate ()-1) as 'the day of the week is today? 'Select DATEPART (hour, getdate () as 'hour' select datepart (Minute, getdate () as 'Min' select datepart (Second, getdate ()) as 'second time' -- 5. the return value of datename indicates the string SELECT datename (weekday, getdate () as 'What is the day of the week? 'Select datename (month, convert (varchar (10), getdate (), 126) as 'What month is today? 'Select datename (day, convert (varchar (10), getdate (), 126) as 'what is today? 'Select datename (year, convert (varchar (10), getdate (), 126) as 'Which year is this year? '-- Last week, Monday select dateadd (wk, DATEDIFF (wk, 0, getdate (),-7) -- last week, day select dateadd (wk, DATEDIFF (wk, 0, getdate (),-1 )--. select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0 )--. select dateadd (wk, datediff (wk, 0, getdate (), 6) on the week of this week -- select dateadd (wk, datediff (wk, 0, getdate () next week ()), 7) -- select dateadd (wk, datediff (wk, 0, getdate (), 13) -- 1. 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 ))--. select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0 )--. select dateadd (wk, datediff (wk, 0, getdate (), 6 )--. select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0 )--. last day of the year SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0 ))--. select dateadd (qq, DATEDIFF (qq, 0, getdate (), 0 )--. 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 ))--. select DATEADD (wk, DATEDIFF (wk, 0, dateadd (dd, 6-datepart (day, getdate (), getdate (), 0) on the first Monday of this month)