If the date/time type operands are given to '-' operator and the types are different from each other, it will be converted to the type with a higher priority. The following example shows that the operand data type on the left is converted from DATE to DATETIME so that the result of '-' operation of DATETIME can be outputted in milliseconds.
SELECT date'2002-01-01' - datetime'2001-02-02 12:00:00 am';
date '2002-01-01'- datetime '2001-02-02 12:00:00 am'
=====================================================
28771200000
If the numeric type operands are given and the types are different from each other, it will be converted to the type with the higher priority.
If the date/time type and the numeric type operands are given to '+' or '-' operator, the numeric type operand is converted to either BIGINT, INT or SHORT.
If a date/time type and a string type are operands, only '+' and '-' operators are allowed. If the '+' operator is used, it will be applied according to the following rules.
SELECT date'2002-01-01' + '10';
date '2002-01-01'+'10'
======================
01/11/2002
If the date/time type and a string type are operands and the '-' operator is used, they will be applied according to the following rules.
SELECT date'2002-01-01'-'2001-01-01';
date '2002-01-01'-'2001-01-01'
================================
31536000000
-- this causes an error
SELECT date'2002-01-01'-'10';
In line 1, column 13,
ERROR: Cannot coerce '10' to type datetime.
If a numeric type and a string type are operands, they will be applied according to the following rules.
SELECT 4 + '5.2';
4+'5.2'
==========================
9.199999999999999e+00
Unlike CUBRID 2008 R3.1 and the earlier versions, the string in the date/time format, that is, the string such as '2010-09-15' is not converted to the date/time type. You can use a literal (DATE'2010-09-15') with the date/time type for addition and subtraction operations.
SELECT '2002-01-01'+1;
ERROR: Cannot coerce '2002-01-01' to type double.
SELECT DATE'2002-01-01'+1;
date '2002-01-01'+1
=====================
01/02/2002
If you multiply, divide or subtract both strings, the result returns a DOUBLE type value.
SELECT '3'*'2';
'3'*'2'
============================
6.000000000000000e+00
The '+' operator action depends on how to set the system parameter plus_as_concat in the cubrid.conf file. For details, see Syntax/Type Related Parameter.
If it is impossible to convert to the corresponding type, an error is returned.