Special Conversion Cases

Conversion of String Literals

The Data Virtuality Server automatically converts string literals within a SQL statement to their implied types. This typically occurs in a criteria comparison where an expression with a different data type is compared to a literal string:

SELECT * FROM my.table WHERE created_by = '2003-01-02'

Here if the created_by column has the data type of date, the Data Virtuality Server automatically converts the string literal to the date data type as well.

If your string literal does not match the default pattern for date (or time or timestamp) datatype, the Data Virtuality Server will implicitly convert the column to a string, and instead of a date/date (time/time, ...) comparison, you will have a string/string comparison executed. This can lead to incorrectly looking result sets. For example, ...WHERE created_by = ‘20030102’ will be executed as ...WHERE cast(created_by as string) = ‘20030102’.

 Converting to Boolean

The Data Virtuality Server can automatically convert literal strings and numeric type values to boolean values as follows:

Date/Time/Timestamp/Datetimeoffset Type Conversions

The Data Virtuality Server can implicitly convert properly formatted literal strings to their associated date-related data types as follows:

The formats above are those expected by the JDBC date types. To use other formats, you can use the functions PARSEDATE, PARSETIME, PARSETIMESTAMP (see Date and Time Functions for more detailed information).