Source: Internet
Author: User

DATEDIFF: Returns the number of date boundaries and time boundaries across two specified dates, syntax: DATEDIFF (datepart, StartDate, enddate) minus EndDate with StartDate
Note: datepart specifies which part of the date should be calculated for the difference of the parameter, whose date is subtracted, only the boundary value is concerned, example select DATEDIFF (year, ' 2008-12-31 ', ' 2009-1-1 ') returns 1

DATEADD: Returns a new datetime value that is added to the specified date after a time interval. Syntax: DATEADD (datepart, number, date)
Note: datepart specifies the part of the date to return the new value
Number used to increase the value of the datepart. A positive number indicates an increase, a negative number is reduced, and if it is a decimal, the fractional part is ignored and no rounding is done.

Calculated by some dates of DATEDIFF and DATEADD

1) First day of the year
SELECT DATEADD (Year,datediff (Year,0,getdate ()), 0)
Note: First DateDiff (Year,0,getdate ())-Calculates the number of years that the current year differs from 1900, and then the first day of the year by calculating the date of 1900-1-1 plus the number of years of difference

2) The first day of a season
SELECT DATEADD (Quarter,datediff (Quarter,0,getdate ()), 0)
Note: First DateDiff (Quarter,0,getdate ())-Calculates the number of quarters between the current month and 1900, then the first day of the season by calculating the date of 1900-1-1 plus the quarter

3) First day of one months
SELECT DATEADD (Month,datediff (Month,0,getdate ()), 0)
Note: First DateDiff (Month,0,getdate ())-Calculates the number of months that the current month differs from 1900, and then the first day of the month by calculating the date of the month in 1900-1-1 plus the difference.

4) The first day of the week
SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

5) Midnight of the Day (00:00:00.000)
SELECT DATEADD (Day,datediff (Day,0,getdate ()), 0)

6) Last day of the month
Note: Subtract 3 milliseconds from the first day of the month, which is the most one day of the month. The time for the SQL SERVER datetime type is accurate to 3 milliseconds.

7) Last day of the month

8) Number of days of the month
II) SELECT 32-day (GETDATE () + (32-day (GETDATE ())))

9) Last day of the year

10) The first day of the week
SELECT DATEADD (Day,1-datepart (Weekday,getdate ()), GETDATE ())
The last day of the week
SELECT DATEADD (Day,7-datepart (Weekday,getdate ()), GETDATE ())

5th-Period conversion function convert CAST
The Style in CONVERT. Parameters: 108 and 114 can only get time.
Example SELECT CONVERT (NVARCHAR (), GETDATE (), 108)---12:41:15
SELECT CONVERT (NVARCHAR, GETDATE (), 12:43:12:590)---

6th Judgment Function ISDATE () determines whether an input expression is a valid date. Returns 0 if valid returns 1, and the return value is INT.

Related Keywords:
Related Article

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.