Comparisons of dates and times

The table below summarizes the conversions that are implicit when comparing certain data types with date, time, or date-time data types.

Data type	Data type	Conversion
CHAR	DATE	CHAR cast to TIMESTAMP; DATE cast to TIMESTAMP
CHAR	TIME	CHAR cast to TIME
CHAR	TIMESTAMP	CHAR cast to TIMESTAMP
CHAR	TIMESTAMP WITH TIME ZONE	CHAR cast to TIMESTAMP WITH TIME ZONE
DATE	TIME	illegal
DATE	TIMESTAMP	DATE cast to TIMESTAMP
DATE	TIMESTAMP WITH TIME ZONE	DATE cast to TIMESTAMP WITH TIME ZONE
DATE	SMALLINT, INTEGER, BIGINT, and NUMERIC	SMALLINT, INTEGER, BIGINT, and NUMERIC value treated as a date string and cast to TIMESTAMP; DATE cast to TIMESTAMP
DATE	REAL, FLOAT, and DOUBLE	REAL, FLOAT, and DOUBLE treated as a number of days since 0000-02-29 and cast to TIMESTAMP; DATE cast to TIMESTAMP
TIME	TIMESTAMP	TIMESTAMP cast to TIME
TIME	TIMESTAMP WITH TIME ZONE	illegal
TIMESTAMP	TIMESTAMP WITH TIME ZONE	TIMESTAMP cast to TIMESTAMP WITH TIME ZONE
TIMESTAMP	SMALLINT, INTEGER, BIGINT, and NUMERIC	SMALLINT, INTEGER, BIGINT, and NUMERIC value treated as a date string and cast to TIMESTAMP
TIMESTAMP	REAL, FLOAT, and DOUBLE	REAL, FLOAT, and DOUBLE treated as a number of days since 0000-02-29 and cast to TIMESTAMP

The following points expand on the information presented in the table above.

Only values of type TIME, TIMESTAMP, and CHAR can be compared to a value of type TIME. Comparison with values of other data types results in a conversion error. When comparing a time value and a value of another type, the comparison data type is TIME.
When comparing a TIMESTAMP, SMALLINT, INTEGER, BIGINT, NUMERIC, REAL, FLOAT, or DOUBLE value to a DATE value, the comparison data type is always TIMESTAMP.
When comparing a TIMESTAMP WITH TIME ZONE value to a DATE value, the comparison data type is TIMESTAMP WITH TIME ZONE.
When a time value is cast to a TIMESTAMP, the result is formed by combining the current date with the time value.
Exact numeric values of type SMALLINT, INTEGER, BIGINT, and NUMERIC can be converted to date values. The conversion is performed by treating the number as a string. For example, the integer value 20100401 represents the first day of April in 2010.
The unsigned exact numeric types BIT, TINYINT, UNSIGNED SMALLINT, UNSIGNED INTEGER, and UNSIGNED BIGINT cannot be converted to date values.
Approximate numeric values of type REAL, FLOAT, and DOUBLE can be converted to dates by treating the number as the number of days since the fictional date 0000-02-29. For example, 307 represents 0001-01-01 and 734169 represents 2010-04-01.