Arithmetic Operation

Date/Time Type Operand

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

Numeric Type Operand

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.

Date/Time Type & Numeric Type Operands

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.

Date/Time Type & String Type Operands

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.    

 

Numeric Type & String Type Operands

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

String Type Operand

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.