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

Source: Internet
Author: User

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)


 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.