SAND CDBMS SQL Reference Guide
Date/Time Arithmetic

Implicit Casting

Certain implicit type casting is performed on quoted string literals to DATE, TIME, and TIMESTAMP values, when they are part of an arithmetic expression or comparison operation. There is also implicit type casting on some numeric expressions to interval types in the same situations.

The implicit date/time type castings are the following:

• a quoted literal in a subtraction expression with a DATE value, or a quoted literal compared to a DATE value, is cast as a DATE value
• a quoted literal in a subtraction expression with a TIME value, or a quoted literal compared to a TIME value, is cast as a TIME value
• a quoted literal in a subtraction expression with a TIMESTAMP value, or a quoted literal compared to a TIMESTAMP value, is cast as a TIMESTAMP value
• a numeric value in an addition or subtraction expression with a time or timestamp interval, or a numeric value compared to a time or timestamp interval, is cast as a SECONDS labeled duration
• a numeric value in an addition or subtraction expression with a date interval, or a numeric value compared to a date interval, is cast as a DAYS labeled duration
• a quoted literal that is added to a date interval, or a quoted literal that has a date interval subtracted from it, is cast as a DATE value
• a quoted literal that is added to a time interval, or a quoted literal that has a time interval subtracted from it, is cast as a TIME value
• a quoted literal that is added to a timestamp interval, or a quoted literal that has a timestamp interval subtracted from it, is cast as a TIMESTAMP value
• a numeric value that is added to a DATE value, or a numeric value subtracted from a DATE value, is cast as a DAYS labeled duration
• a numeric value that is added to a TIME or TIMESTAMP value, or a numeric value subtracted from a TIME or TIMESTAMP value, is cast as a SECONDS labeled duration
• a numeric value subtracted from a quoted literal, or a numeric value added to a quoted literal, involves a double casting: if the quoted literal is a valid date, it is cast as a DATE value and the numeric is cast as a DAYS labeled duration; if the quoted literal is a valid time, it is cast as a TIME value and the numeric is cast as a SECONDS labeled duration; or if the quoted literal is a valid timestamp, it is cast as a TIMESTAMP value and the numeric is cast as a SECONDS labeled duration
• a quoted literal subtracted from a quoted literal involves a double casting: if both quoted literals are valid dates, they are both cast as DATE values; if both quoted literals are valid times, they are both cast as TIME values; or if both quoted literals are valid timestamps, they are both cast as TIMESTAMP values.

Examples

Expression	Result
'01:01:01' + 59	01:02:00
'1945-09-02' - '1939-09-10'	2184 (days)
'2003-12-19' - 19	2003-11-30