MIDNIGHT_SECONDS
The MIDNIGHT_SECONDS function returns an integer value that is greater than or equal to 0 and less than or equal to 86 400 representing the number of seconds between midnight and the time value specified in the argument.
- expression
- An expression that returns a value of one of the following built-in
data types: a date, a time, a timestamp, a character
string, or a graphic string. Its value must be a valid string representation
of a time or timestamp. For the valid formats of string representations
of times and timestamps, see String representations of datetime values.
If the argument is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).
The result of the function is large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Examples
- Find the number of seconds between midnight and 00:01:00, and
midnight and 13:10:10. Assume that host variable XTIME1 has a value
of '00:01:00', and that XTIME2 has a value of '13:10:10'.
This example returns 60 and 47410. Because there are 60 seconds in a minute and 3600 seconds in an hour, 00:01:00 is 60 seconds after midnight ((60 * 1) + 0), and 13:10:10 is 47410 seconds ((3600 * 13) + (60 * 10) + 10).SELECT MIDNIGHT_SECONDS(:XTIME1), MIDNIGHT_SECONDS(:XTIME2) FROM SYSIBM.SYSDUMMY1 - Find the number of seconds between midnight and 24:00:00, and
midnight and 00:00:00.
This example returns 86400 and 0. Although these two values represent the same point in time, different values are returned.SELECT MIDNIGHT_SECONDS('24:00:00'), MIDNIGHT_SECONDS('00:00:00') FROM SYSIBM.SYSDUMMY1