http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/dd48aff6/docs/2.3.0/sql_reference/dependencies.html ---------------------------------------------------------------------- diff --git a/docs/2.3.0/sql_reference/dependencies.html b/docs/2.3.0/sql_reference/dependencies.html index 1591701..f926c4d 100644 --- a/docs/2.3.0/sql_reference/dependencies.html +++ b/docs/2.3.0/sql_reference/dependencies.html @@ -1,5 +1,5 @@ - + @@ -10,7 +10,7 @@ @import url("./css/site.css"); - + @@ -27,7 +27,7 @@
- Last Published: 2017-11-15 + Last Published: 2017-11-26  | Version: 2.3.0
Trafodion SQL Reference Manual http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/dd48aff6/docs/2.3.0/sql_reference/index.html ---------------------------------------------------------------------- diff --git a/docs/2.3.0/sql_reference/index.html b/docs/2.3.0/sql_reference/index.html index 05bed56..51a5534 100644 --- a/docs/2.3.0/sql_reference/index.html +++ b/docs/2.3.0/sql_reference/index.html @@ -1404,10 +1404,13 @@ table.CodeRay td.code>pre{padding:0}
  • 7.36. DATEDIFF Function
  • 7.37. DATE_PART Function (of an Interval) @@ -31545,8 +31548,7 @@ returned. See Datetime Value Expressions7.36. DATEDIFF Function

    The DATEDIFF function returns the integer value for the number of -datepart units of time between startdate and enddate. If -enddate precedes startdate, the return value is negative or zero.

    +datepart units of time between startdate and enddate.

  • +
    +
    +

    7.36.1. Syntax Description of DATEDIFF

    +
    +
    +

    7.36.2. Considerations for DATEDIFF

    +
    +
    Boundary
    -

    The method of counting crossed boundaries such as days, minutes, and -seconds makes the result given by DATEDIFF consistent across all data -types. The result is a signed integer value equal to the number of -datepart boundaries crossed between the first and second date.

    -
    -
    -

    For example, the number of weeks between Sunday, January 4, and Sunday, -January 1 , is 1. The number of months between March 31 and April 1 -would be 1 because the month boundary is crossed from March to April. -The DATEDIFF function generates an error if the result is out of range -for integer values. For seconds, the maximum number is equivalent to -approximately 68 years. The DATEDIFF function generates an error if a -difference in weeks is requested and one of the two dates precedes -January 7 of the year 0001.

    +

    The method of counting crossed boundaries such as days, minutes, and seconds makes the result given by DATEDIFF consistent across all data types.

    +
    +
    +

    The result is a signed integer value equal to the number of datepart boundaries crossed between startdate and enddate, because the DATEDIFF function does not calculate the full datepart, it counts the difference between startdate and enddate.

    +
    +
    +

    For example:

    +
    +
    +
      +
    • +

      The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is only a single second.

      +
      +

      However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week1, 1 month, 1 quarter or 1 month depending on the specified datepart.

      +
      +
      +

      1 NOTE: 2017-12-31 (startdate) falls on a Sunday, which is in the same week as 2018-01-01. For more information, see The first day of week.

      +
      +
    • +
    • +

      Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two years.

      +
      +

      However, the DATEDIFF difference is 1 year if the specified datepart is YEAR.

      +
      +
    • +
    +
    +
    +
    +
    The first day of week
    +
    +
      +
    • +

      The first day of the week is Sunday. Changing the first day of the week is not supported.

      +
    • +
    • +

      The DATEDIFF function returns the number of Sundays between startdate and enddate. This function counts enddate if it falls on a Sunday, but doesn’t count startdate even if it does fall on a Sunday.

      +
      +

      For example,

      +
      +
      +
        +
      • +

        The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the specified datepart is WEEK.

        +
      • +
      • +

        The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the specified datepart is WEEK.

        +
      • +
      +
      +
    • +
    +
    +
    +
    +
    The first week of year
    +
    +

    The first week of the year is the week in which January 1 occurs. Changing the first week of the year is not supported.

    +
    +
    +
    +
    Negative or zero
    +
    +

    If enddate precedes startdate, the return value is negative or zero.

    +
    +
    +
    +
    Error
    +
    +
      +
    • +

      The DATEDIFF function generates an error if the result is out of range for integer values.

      +
      +
        +
      • +

        For seconds, the maximum number is equivalent to approximately 68 years.

        +
      • +
      • +

        For weeks, the dates must be later than 0001-01-07.

        +
      • +
      +
      +
    • +
    +
    +
    -

    7.36.1. Examples of DATEDIFF

    +

    7.36.3. Examples of DATEDIFF

    +
    +
    Date Difference in SECOND
    • -

      This function returns the value of 0 because no one-second boundaries -are crossed.

      +

      This function returns the value of 0 because no one-second boundary +is crossed.

      -
      DATEDIFF( SECOND
      -        , TIMESTAMP '2006-09-12 11:59:58.999998'
      -        , TIMESTAMP '2006-09-12 11:59:58.999999'
      -        )
      +
      SELECT DATEDIFF( SECOND
      +                 , TIMESTAMP '2006-09-12 11:59:58.999998'
      +                 , TIMESTAMP '2006-09-12 11:59:58.999999'
      +               )
      +FROM DUAL;
    • -

      This function returns the value 1 because a one-second boundary is +

      This function returns the value of 1 because a one-second boundary is crossed even though the two timestamps differ by only one microsecond.

      -
      DATEDIFF( SECOND
      -        , TIMESTAMP '2006-09-12 11:59:58.999999'
      -        , TIMESTAMP '2006-09-12 11:59:59.000000'
      -        )
      +
      SELECT DATEDIFF( SECOND
      +                 , TIMESTAMP '2006-09-12 11:59:58.999999'
      +                 , TIMESTAMP '2006-09-12 11:59:59.000000'
      +               )
      +FROM DUAL;
    • +
    +
    +
    +
    +
    Date Difference in MINUTE
    +
    +
    • -

      This function returns the value of 0.

      +

      This function returns the value of 2 because two minute boundaries are crossed.

      -
      DATEDIFF( YEAR
      -        , TIMESTAMP '2006-12-31 23:59:59.999998'
      -        , TIMESTAMP '2006-12-31 23:59:59.999999'
      -        )
      +
      SELECT DATEDIFF( MINUTE
      +                 , TIMESTAMP '2011-12-30 08:54:55'
      +                 , TIMESTAMP '2011-12-30 08:56:01'
      +               )
      +FROM DUAL;
    • +
    +
    +
    +
    +
    Date Difference in DAY
    +
    +
    • -

      This function returns the value of 1 because a year boundary is -crossed.

      +

      This function returns the value of -29.

      -
      DATEDIFF( YEAR
      -        , TIMESTAMP '2006-12-31 23:59:59.999999'
      -        , TIMESTAMP '2007-01-01 00:00:00.000000'
      -        )
      +
      SELECT DATEDIFF( DAY
      +                 , DATE '2008-03-01'
      +                 , DATE '2008-02-01'
      +               )
      +FROM DUAL;
    • -

      This function returns the value of 2 because two WEEK boundaries are +

      This statement calculates how long buyers have to wait.

      +
      +
      +
      SELECT id, DATEDIFF( DAY, order_date, delivery_date ), price
      +FROM orders
      +ORDER BY price DESC;
      +
      +
      +
      +
      +
      ID           (EXPR)       PRICE
      +-----------  -----------  ------
      +
      +     700300          145     926
      +     312203          147     621
      +     800660           23     568
      +     100350          160     543
      +     500450          148     324
      +     700510          141     229
      +     100210            3     228
      +     600480          151     197
      +     300380          154     183
      +     200490          227     123
      +     200320          153      91
      +     400410          158      65
      +     600250          143      32
      +
      +--- 13 row(s) selected.
      +
      +
      +
    • +
    +
    +
    +
    +
    Date Difference in WEEK
    +
    +
      +
    • +

      This function returns the value of 1 because only a one-week boundary is crossed.

      -
      DATEDIFF(WEEK, DATE '2006-01-01', DATE '2006-01-09')
      +
      SELECT DATEDIFF( WEEK
      +                 , DATE '2006-01-01'
      +                 , DATE '2006-01-09'
      +               )
      +FROM DUAL;
    • +
    +
    +
    +
    +
    Date Difference in QUARTER
    +
    +
    • -

      This function returns the value of -29.

      +

      This function returns the value of 3 because three quarter boundaries are crossed.

      +
      +
      +
      SELECT DATEDIFF( QUARTER
      +                 , DATE '2017-03-05'
      +                 , DATE '2017-11-17'
      +               )
      +FROM DUAL;
      +
      +
      +
    • +
    +
    +
    +
    +
    +

    7.36.4. Date Difference in YEAR

    +
    +
    -

    7.36.2. DATEFORMAT Function

    +

    7.36.5. DATEFORMAT Function

    The DATEFORMAT function returns a datetime value as a character string literal in the DEFAULT, USA, or EUROPEAN format. The data type of the @@ -31744,14 +31932,14 @@ or TIMESTAMP. See Datetime Value Expressio

    -

    7.36.3. Considerations for DATEFORMAT

    +

    7.36.6. Considerations for DATEFORMAT

    The DATEFORMAT function returns the datetime value in ISO88591 encoding.

    -

    7.36.4. Examples of DATEFORMAT

    +

    7.36.7. Examples of DATEFORMAT