Verbose SQL in DateDiff usage

Source: Internet
Author: User

DATEDIFF function [date and time]

Function
Returns the interval between two dates.

Grammar
DATEDIFF (Date-part, date-expression-1, date-expression-2)

Date-part:
Year | Quarter | Month | Week | Day | Hour | Minute | Second | Millisecond

Parameters
DATE-PART Specifies the date portion of the interval to be measured.

For more information about the date section, see the Date section.

Date-expression-1 the start date of a certain interval. Subtracts the value from Date-expression-2 and returns the number of days date-parts between two parameters.

Date-expression-2 the end date of a certain interval. Subtract date-expression-1 from this value to return the number of days between the two parameters date-parts.

Usage
This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date2-date1) in the date part.

When the result is not an even multiple of the date part, the DATEDIFF is truncated instead of rounded.

When day is used as the date part, DATEDIFF returns the number of midnight between two specified times, including the second date but excluding the first date.

When you use month as the date part, DATEDIFF returns the number of the first day of the month that occurs between two dates, including the second date but excluding the first date.

When you use week as the date part, DATEDIFF returns the number of Sunday between two dates, including the second date but excluding the first date.

For a smaller time unit there is an overflow value:

Milliseconds 24 days

Seconds 68

Minutes 4083

Others no overflow limit

If these limits are exceeded, this function returns an overflow error.

Standards and compatibility
sql/92 transact-SQL extension.

sql/99 transact-SQL extension.

Sybase is compatible with Adaptive Server Enterprise.

The statement in the following example returns 1:

The statement under SELECT DateDiff (hour, ' 4:00AM ', ' 5:50am ') returns 102:

SELECT DateDiff (month, ' 1987/05/02 ', ' 1995/11/15 ') The following statement returns 0:

SELECT DateDiff (Day, ' 00:00 ', ' 23:59 ') The following statement returns 4:

SELECT DateDiff (Day,
' 1999/07/19 00:00 ',
' 1999/07/23 23:59 ') The following statement returns 0:

SELECT DateDiff (month, ' 1999/07/19 ', ' 1999/07/23 ') The following statement returns 1:

SELECT DateDiff (month, ' 1999/07/19 ', ' 1999/08/23 ')

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.