	Note:
	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.

9.1.1. Date Literals

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.

9.1.2. Casting

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.

Example 9.1. Examples:

select cast ('2000-1-3' as date);

select cast (cast ('2000-1-3' as date) as varchar);
	= 2000-01-03 00-00-00 000000

9.1.3. Time & Date Manipulation

The SQL92 standard functions for time and date queries are available. These are mapped to Virtuoso internal functions as follows:

CURRENT_DATE -

curdate()

CURRENT_TIME -

curtime()

CURRENT_TIMESTAMP -

curdatetime()

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:

Table 9.1. Time & date syntax

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)

Example 9.2. Example: comparing dates

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.

9.1.4. Declaring Collations of Expressions

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.

Prev		Next
8.14. Parallel Operations and Bulk Data Transfer with Remote Tables	Home	9.2. User Defined Types

Prefix	IRI
schema	http://schema.org/
n5	http://creativecommons.org/licenses/by/4.0/
n4	http://docs.openlinksw.com/virtuoso/ch-sqlreference/
rdf	http://www.w3.org/1999/02/22-rdf-syntax-ns#
n3	http://www.openlinksw.com/#
xsdh	http://www.w3.org/2001/XMLSchema#

Prefix	URI
xmlns:schema	http://schema.org/
xmlns:n5	http://creativecommons.org/licenses/by/4.0/
xmlns:n4	http://docs.openlinksw.com/virtuoso/ch-sqlreference/
xmlns:rdf	http://www.w3.org/1999/02/22-rdf-syntax-ns#
xmlns:n3	http://www.openlinksw.com/#
xmlns:xsdh	http://www.w3.org/2001/XMLSchema#

Subject	Predicate	Object
n4:	rdf:type	schema:TechArticle
n4:	schema:name	ChapterÃÂ 9.ÃÂ SQL Reference
n4:	schema:copyrightHolder	_:vb78676
n4:	schema:datePublished	2016-09-09 16:16:54
n4:	schema:description	SQL Reference.
n4:	schema:headline	ChapterÃÂ 9.ÃÂ SQL Reference
n4:	schema:keywords	OpenLink,Virtuoso,database,RDBMS,relational,SQL,RDF,triple store,linked data,linked open data,Big Data,ODBC
n4:	schema:license	n5:deed.en_US
n4:	schema:publisher	_:vb78675
n4:	schema:url	n4:
_:vb78675	rdf:type	schema:Organization
_:vb78675	schema:name	OpenLink Software
_:vb78675	schema:url	n3:this
_:vb78676	rdf:type	schema:Organization
_:vb78676	schema:name	OpenLink Software
_:vb78676	schema:url	n3:this

Chapter 9. SQL Reference

9.1. Datatypes