CHARACTER: |
CHARACTER VARCHAR VARCHAR'('INTNUM')' NVARCHAR NVARCHAR'('INTNUM')' CHAR'('INTNUM')' |
NUMERIC: |
NUMERIC NUMERIC'('INTNUM')' NUMERIC'('INTNUM','INTNUM')' |
DECIMAL: |
DECIMAL DECIMAL'('INTNUM')' DECIMAL'('INTNUM','INTNUM')' |
INTEGER: |
INT INTEGER SMALLINT |
FLOAT: |
FLOAT FLOAT'('INTNUM')' |
REAL: |
REAL DOUBLE PRECISION |
LONG VARCHAR: |
BLOB data |
VARBINARY [( precision )]: |
Binary data |
LONG VARBINARY: |
Binary BLOB data |
TIMESTAMP: |
TIMESTAMP DATETIME TIME DATE |
<UDT>: |
User Defined Type with varbinary-like size properties. |
LONG <UDT>: |
User Defined Type with LONG varbinary-like size properties. |
LONG XML: |
LONG XML BLOB-like data type. |
User Defined Types can be created from native or external types, composites or classes from any hosted language such as Java or .Net. Any User Defined Type can be used to define a column in a CREATE TABLE statement.
Virtuoso does not support date literals or the DATE reserved keyword. Literal dates should be enclosed in a conversion function such as stringdate(), as in this example:
select * from demo.demo.orders o where o.orderdate between stringdate('1994-01-01') And stringdate('1997-12-31')
Alternatively type casts can be used to explicitly instruct Virtuoso to assume a string as a date, see below.
Blob types can be cast to varchars. This will produce a string of up to 16 MB in length and an error for longer blobs.
Nothing can be cast to a blob type. Blobs only come from selecting blob columns and are created by assigning values to such columns.
Converting non-integer values to integers rounds towards 0.
Any data type can be converted to binary with the VARBINARY target data type. The result may or may not be meaningful. Specifically datetime can be converted to binary and back.
cast_expression : CAST '(' scalar_exp AS data_type [COLLATE collation_name ] ')' collation_name : identifier | owner '.' identifier | qualifier '.' owner '.' identifier
The CAST expression converts the type of the scalar_exp into the data_type, if possible. If the conversion is not meaningful, as from a float to a date, an error is signalled.
CAST is the recommended way of converting between data types, including any conversion between strings, different number types and datetime types.
select cast ('2000-1-3' as date); select cast (cast ('2000-1-3' as date) as varchar); = 2000-01-03 00-00-00 000000
The SQL92 standard functions for time and date queries are available. These are mapped to Virtuoso internal functions as follows:
The results of the above functions can also be obtained using the now() function and casting to the appropriate target type.
Dates and times should be input or compared as literals in a standard format. The following table describes the proper methods available:
Datatype | ODBC Syntax | SQL92 Casting |
---|---|---|
Date | {d 'yyyy-mm-dd'} | cast('yyyy-mm-dd' as date) |
Time | {t 'hh:mm:ss'} | cast('hh:mm:ss' as time) |
Datetime/timestamp | {ts 'yyyy-mm-dd hh:mm:ss[.f...]'} | cast('yyyy-mm-dd hh:mm:ss[.f...]' as datetime) |
select datecol from table where datecol > cast('1900-01-01' as date) and datecol < cast(now() as date);
now() is cast for explicit compatibility although not required.
A collation can be declared with CAST for string expressions. Suppose insensitive_ascii were a collation name specifying case insensitive comparison of ASCII strings, the predicate:
CAST 'foo' as varchar collate insensitive_ascii) = CAST 'FOO as varchar collate insensitive_ascii)
would be true.
This will alter the semantic of string comparison and can be used to define a collation where the collation would otherwise be default, for instance when comparing results of expressions or constants. This can also alter the default collation implied by the collation declaration in column options in CREATE TABLE.
Previous
Contents of SQL Reference |
Chapter Contents |
Next
User Defined Types |