Hitachi

Hitachi Advanced Database SQL Reference

8.9.1 DATEDIFF

Returns the difference between the start date and time and the end date and time.

Organization of this subsection

(1) Specification format

scalar-function-DATEDIFF ::= DATEDIFF(datetime-unit,start-datetime,end-datetime)
 
  datetime-unit ::= {YEAR|QUARTER|MONTH|WEEK|DAY
                    |DAYOFYEAR|HOUR|MINUTE|SECOND
                    |MILLISECOND|MICROSECOND|NANOSECOND|PICOSECOND}
  start-datetime ::= value-expression
  end-datetime ::= value-expression

To Page Top

(2) Explanation of specification format

datetime-unit:

Specifies the unit to be used when determining the difference between start-datetime and end-datetime. Specify one of the following values:

  • YEAR

    Specify this to determine the difference in years between start-datetime and end-datetime.

    Examples

    DATEDIFF(YEAR,'2011-05-05','2013-07-10') → 2

    DATEDIFF(YEAR,'2013-05-05','2013-07-10') → 0

    DATEDIFF(YEAR,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • QUARTER

    Specify this to determine the difference in quarters between start-datetime and end-datetime. Quarters are calculated as three-month periods beginning January 1.

    • First quarter: January 1 to March 31

    • Second quarter: April 1 to June 30

    • Third quarter: July 1 to September 30

    • Fourth quarter: October 1 to December 31

    Examples

    DATEDIFF(QUARTER,'2013-01-05','2013-07-10') → 2

    DATEDIFF(QUARTER,'2013-01-05','2013-03-10') → 0

    DATEDIFF(QUARTER,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • MONTH

    Specify this to determine the difference in months between start-datetime and end-datetime.

    Examples

    DATEDIFF(MONTH,'2013-01-05','2013-07-10') → 6

    DATEDIFF(MONTH,'2013-01-05','2013-01-10') → 0

    DATEDIFF(MONTH,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • WEEK

    Specify this to determine the difference in weeks between start-datetime and end-datetime. Weeks are calculated as beginning on Sunday.

    Examples

    DATEDIFF(WEEK,'2013-07-05','2013-07-10') → 1

    This example returns 1 because the week changes on July 7, 2013, which is a Sunday.

    DATEDIFF(WEEK,'2012-12-30','2013-01-01') → 0

    This example returns 0 because December 30, 2012 is a Sunday, so the week does not change.

  • DAY

    Specify this to determine the difference in days between start-datetime and end-datetime.

    Examples

    DATEDIFF(DAY,'2013-07-05','2013-07-10') → 5

    DATEDIFF(DAY,'2013-07-05 08:02:25','2013-07-05 17:55:18') → 0

    DATEDIFF(DAY,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • DAYOFYEAR

    Specify this to determine the difference between start-datetime and end-datetime in terms of cumulative number of days. It returns the same result as when DAY is specified.

    Examples

    DATEDIFF(DAYOFYEAR,'2013-07-05','2013-07-10') → 5

    DATEDIFF(DAYOFYEAR,'2013-07-05 08:02:25','2013-07-05 17:55:18') → 0

    DATEDIFF(DAYOFYEAR,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • HOUR

    Specify this to determine the difference in hours between start-datetime and end-datetime.

    Examples

    DATEDIFF(HOUR,'2013-07-10 08:02:25','2013-07-10 11:37:55') → 3

    DATEDIFF(HOUR,'2013-07-10 08:02:25','2013-07-10 08:45:15') → 0

    DATEDIFF(HOUR,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • MINUTE

    Specify this to determine the difference in minutes between start-datetime and end-datetime.

    Examples

    DATEDIFF(MINUTE,'2013-07-10 08:02:25','2013-07-10 08:07:25') → 5

    DATEDIFF(MINUTE,'2013-07-10 08:02:25','2013-07-10 08:02:32') → 0

    DATEDIFF(MINUTE,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • SECOND

    Specify this to determine the difference in seconds between start-datetime and end-datetime.

    Examples

    DATEDIFF(SECOND,'2013-07-10 08:02:25','2013-07-10 08:02:33') → 8

    DATEDIFF(SECOND,'2012-12-31 23:59:59','2013-01-01 00:00:00') → 1

  • MILLISECOND

    Specify this to determine the difference in milliseconds (1/1,000 seconds) between start-datetime and end-datetime.

    Examples

    DATEDIFF(MILLISECOND,'08:02:25.000','08:02:25.003') → 3

    DATEDIFF(MILLISECOND,'08:02:24.000','08:02:25.001') → 1001

    DATEDIFF(MILLISECOND,'08:02:25.000000','08:02:25.003111') → 3

  • MICROSECOND

    Specify this to determine the difference in microseconds (1/1,000,000 seconds) between start-datetime and end-datetime.

    Example

    DATEDIFF(MICROSECOND,'08:02:25.000000','08:02:25.000012') → 12

  • NANOSECOND

    Specify this to determine the difference in nanoseconds (1/1,000,000,000 seconds) between start-datetime and end-datetime.

    Example

    DATEDIFF(NANOSECOND,'08:02:25.000000000','08:02:25.000000123') → 123

  • PICOSECOND

    Specify this to determine the difference in picoseconds (1/1,000,000,000,000 seconds) between start-datetime and end-datetime.

    Example

    DATEDIFF(PICOSECOND,'08:02:25.000000000000','08:02:25.000000000003') → 3

start-datetime:

Specifies the start datetime.

The following rules apply:

  • Specify start-datetime in the form of a value expression. For details about value expressions, see 7.20 Value expression.

  • The data type of start-datetime must be DATE, TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the predefined input representation formats. For details about the predefined input representations, see 6.3.3 Predefined character-string representations.

  • You cannot specify a dynamic parameter by itself for start-datetime.

end-datetime:

Specifies the end datetime.

The following rules apply:

  • Specify end-datetime in the form of a value expression. For details about value expressions, see 7.20 Value expression.

  • The data type of end-datetime must be DATE, TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the predefined input representation formats. For details about the predefined input representations, see 6.3.3 Predefined character-string representations.

  • You cannot specify a dynamic parameter by itself for end-datetime.

To Page Top

(3) Rules

  1. For the execution result, the value obtained by subtracting start-datetime from end-datetime is returned. If end-datetime is earlier than start-datetime, a negative value is returned.

  2. When the hour, minutes, and seconds are missing, for example when start-datetime is DATE type and end-datetime is TIMESTAMP type, the hour, minutes, and seconds are assumed to be 00:00:00. When the fractional seconds are missing, all the missing digits are assumed to be 0.

    Example: DATEDIFF(SECOND,'2013-07-10','2013-07-10 00:00:07') → 7

    In the example above, start-datetime is assumed to be 2013-07-10 00:00:00.

  3. If you specify a DATE type, TIMESTAMP type, predefined character-string representation of a date, or a predefined character-string representation of a time stamp for start-datetime, you must also specify a DATE type, TIMESTAMP type, predefined character-string representation of a date, or a predefined character-string representation of a time stamp for end-datetime.

  4. If you specify a TIME type or a predefined character-string representation of a time for start-datetime, you must also specify a TIME type or a predefined character-string representation of a time for end-datetime.

  5. If you specify a TIME type or a predefined character-string representation of a time for start-datetime and end-datetime, and YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, or WEEK for datetime-unit, the value of the execution result will be 0.

  6. The data type of the execution result is the INTEGER type. An error results if the execution result exceeds the range that can be represented by the INTEGER type. For the range that can be represented by the INTEGER type, see (1) Numeric data in 6.2.1 List of data types.

  7. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

  8. If start-datetime or end-datetime has a null value, the execution result will be a null value.

To Page Top

(4) Example

Example:

Determine the difference in days between the datetime data in columns C1 and C2 from table T1.

SELECT DATEDIFF(DAY,"C1","C2") FROM "T1"

[Figure]

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.