IS INGRESDATE, IS ANSIDATE, IS TIME, IS TIMESTAMP, IS INTERVAL YEAR TO MONTH, IS INTERVAL DAY TO SECOND Predicates

SQL Reference Guide > SQL Reference Guide > Understanding the Elements of SQL Statements > Predicates in SQL > IS INGRESDATE, IS ANSIDATE, IS TIME, IS TIMESTAMP, IS INTERVAL YEAR TO MONTH, IS INTERVAL DAY TO SECOND Predicates

Was this helpful?

Use these predicates to determine if a result can be assigned to a column of the given data type without loss of data. They are especially useful for cleaning data or validating data input. When applied to row value expressions, all elements must test the same.

These predicates take the following form:

IS [NOT] INGRESDATE

IS [NOT] ANSIDATE

IS [NOT] TIME [(precision)]

IS [NOT] TIMESTAMP [(precision)]

IS [NOT] INTERVAL YEAR TO MONTH

IS [NOT] INTERVAL DAY TO SECOND [(precision)]

For example:

'2006-07-12 12:06:22.2143' IS TIMESTAMP(3)

is false as precision would be lost.

'12:06:22.2143' IS TIME(6)

is true as precision and value would be retained.

SELECT 'date' IS INGRESDATE

is true if date is INGRESDATE in form.

If all values in a column pass this predicate, then the whole column can be coerced into an INGRESDATE column.

For example, the following returns "Invalid":

SELECT CASE WHEN 'abc' IS INGRESDATE THEN 'Valid' ELSE 'Invalid' END

INGRESDATE columns can also be checked, such as with this query that identifies any rows that could not be converted to an ANSIDATE:

SELECT val FROM table WHERE val IS NOT ANSIDATE

Last modified date: 11/28/2023