MINUTES function [Date and time]

The MINUTES function manipulates a TIMESTAMP, or returns the number of minutes between two TIMESTAMP values. See the Remarks section below.

Syntax 1

MINUTES( timestamp-expression )

Syntax 2

MINUTES( timestamp-expression, timestamp-expression )

Syntax 3

MINUTES( timestamp-or-time-expression, integer-expression )

Parameters

timestamp-expression An expression of type TIMESTAMP.
timestamp-or-time-expression An expression of type TIME or TIMESTAMP.
integer-expression The number of minutes to be added to timestamp-or-time-expression. If integer-expression is negative, the appropriate number of minutes is subtracted from timestamp-or-time-expression.

Remarks

The result of the MINUTES function depends on its arguments.

Syntax 1 If you pass a single timestamp-expression to the MINUTES function, it will return the number of minutes between midnight 0000-02-29 and timestamp-expression as an INTEGER.

Note

0000-02-29 is not meant to imply an actual date; it is the default date used by the MINUTES function.
Syntax 2 If you pass two TIMESTAMP values to the MINUTES function, the function returns the integer number of minutes between them.
Syntax 3 If you pass a TIMESTAMP value and an INTEGER value to the MINUTES function, the function returns the TIMESTAMP result of adding the integer number of minutes to timestamp-expression argument. Similarly, if the first argument to MINUTES is a TIME value, then the result is also a TIME value. Syntax 3 does not support implicit conversion of the first argument. It may be necessary to explicitly cast the first argument to a DATE, TIME or TIMESTAMP value. If the first argument is of type DATE, midnight is assumed for the time portion.

Since MINUTES returns an integer, overflow can occur when Syntax 1 is used with TIMESTAMP values greater than or equal to 4083-03-23 02:08:00.

Instead of Syntax 2, use the DATEDIFF function. Instead of Syntax 3, use the DATEADD function.

Example

The following statements return the value 240, signifying that the second TIMESTAMP value is 240 minutes after the first. It is recommended that you use the second example (DATEDIFF).

SELECT MINUTES( '1999-07-13 06:07:12',
    '1999-07-13 10:07:12' );

SELECT DATEDIFF( minute,
     '1999-07-13 06:07:12',
     '1999-07-13 10:07:12' );

The following statement returns the value 1051040527.

SELECT MINUTES( '1998-07-13 06:07:12' );

The following statements return the TIMESTAMP value 1999-05-12 21:10:07.000. Note that the first statement requires an explicit cast of the literal string parameter. It is recommended that you use the second example (DATEADD).

SELECT MINUTES( CAST( '1999-05-12 21:05:07' AS TIMESTAMP ), 5); 

SELECT DATEADD( minute, 5, '1999-05-12 21:05:07' );

The following statement returns 'TIME', illustrating that the MINUTES function returns a TIME value when it is called with a TIME argument.

SELECT EXPRTYPE('SELECT MINUTES( CAST( ''13:45:00.000'' AS TIME ), 16 )', 1);

Discuss this page in DocCommentXchange.