SQL - Scalar functions - YMD_BETWEEN

YMD_BETWEEN

The YMD_BETWEEN function returns a numeric value that specifies the number of full years, full months, and full days between two datetime values.

The schema is SYSIBM.

expression1: An expression that specifies the first datetime value to compute the number of full years, full months, and full days between two datetime values.
The expression must return a value that is a DATE, TIMESTAMP, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If expression1 is a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it must be a valid string that is accepted by the TIMESTAMP scalar function.
expression2: An expression that specifies the second datetime value to compute the number of full years, full months, and full days between two datetime values.
The expression must return a value that is a DATE, TIMESTAMP, CHAR, or VARCHAR data type.
In a Unicode database, the expression can also be a GRAPHIC or VARGRAPHIC data type.
CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC are supported by using implicit casting.
If expression2 is a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it must be a valid string that is accepted by the TIMESTAMP scalar function.

If there is less than a full day between expression1 and expression2, the result is zero.
If expression1 is later than expression2, the result is positive.
If expression1 is earlier than expression2, the result is negative.
If expression1 or expression2 contains time information, this information is also used to determine the number of full years, full months, and full days.
If expression1 or expression2 does not contain time information, a time of midnight (00.00.00) is used for the argument that is missing time information.

The result of the function is an INTEGER.
If either argument can be null, the result can be null.
If either argument is null, the result is the null value.

The YMD_BETWEEN function is a synonym of the following expression:

INTEGER( ( TIMESTAMP(expression1, 12) - TIMESTAMP(expression2, 12) ) / 1000000 )

The result is the integer representation of the extraction of the year, month, and day components of a timestamp duration.

Examples

Set the host variable YMD to the number of full years, full months, and full days between 2013-09-23-23.59.59.123456789012 and
2013-09-24-23.59.59.123456789011.
```
   SET :YMD = YMD_BETWEEN(TIMESTAMP '2013-09-24-23.59.59.123456789011', 
                          TIMESTAMP '2013-09-23-23.59.59.123456789012')
```
The host variable YMD is set to 0 because there are 0.000000000001 seconds less than a full day between the arguments.
It is positive because the first argument is later than the second argument.

Set the host variable YMD to the number of full years, full months, and full days between 2013-09-23-23.59.59.123456789012 and
2013-09-24-23.59.59.123456789012.
```
   SET :YMD = YMD_BETWEEN(TIMESTAMP '2013-09-24-23.59.59.123456789012',
                          TIMESTAMP '2013-09-23-23.59.59.123456789012')
```
The host variable YMD is set to 1 because there is exactly 1 day between the arguments.
It is positive because the first argument is later than the second argument.

Set the host variable YMD to the number of full years, full months, and full days between 2013-09-23-23.59.59.123456789012 and
2016-03-01-23.59.59.123456789011.
```
   SET :YMD = YMD_BETWEEN(TIMESTAMP '2013-09-23-23.59.59.123456789012', 
                          TIMESTAMP '2016-03-01-23.59.59.123456789011')
```
The host variable YMD is set to -20507 because there are 0.000000000001 seconds less than 2 full years, 5 full months, and 8 full days between the arguments.
It is negative because the first argument is earlier than the second argument.