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
(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.
-
(3) Rules
-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If start-datetime or end-datetime has a null value, the execution result will be a null value.
(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"