Description

The IS_VALID_TIMESTAMP() function returns either true or false depending on whether the specified value is a valid timestamp or not. The minimum valid timestamp equates to the beginning of the year 1583. That is, the first microsecond of that year. The maximum valid timestamp equates to the last microsecond of the year 9999.

Because TIMESTAMP values are stored and can be entered as an eight byte integer, it is possible to enter a numeric value that is not actually a valid timestamp. The functions MIN_VALID_TIMESTAMP() and MAX_VALID_TIMESTAMP() give you access to the valid minimum and maximum values. The function IS_VALID_TIMESTAMP() compares a TIMESTAMP value and returns true or false depending on whether the value falls within the valid range or not.

Example

The following example uses the TIMESTAMP functions to return an informational string for any event records that contain an invalid timestamp value.

SELECT 'TIMESTAMP must be between ' || 
        CAST(MIN_VALID_TIMESTAMP() as VARCHAR) ||
        ' and ' ||
        CAST(MAX_VALID_TIMESTAMP() as VARCHAR),
        log_time,
        log_event
   FROM events WHERE NOT IS_VALID_TIMESTAMP(log_time);