<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href=".html" title="" />
<link rel="next" href="udt.html" title="User Defined Types" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="sqlrefDATATYPES" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="sqlreference.html" title="SQL Reference">Prev</a> | <a class="link" href="udt.html" title="User Defined Types">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div class="selected">
<a href="sqlrefDATATYPES.html">Datatypes</a>
<div>
<a href="#dt_dateliterals" title="Date Literals">Date Literals</a>
<a href="#dtcasting" title="Casting">Casting</a>
<a href="#sqldatemanus" title="Time & Date Manipulation">Time & Date Manipulation</a>
<a href="#deccolexp" title="Declaring Collations of Expressions">Declaring Collations of Expressions</a>
</div>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="sqlrefDATATYPES" />
<h2>8.1. Datatypes</h2>
<table class="varlist">
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">CHARACTER:</td>
<td>
<p>CHARACTER</p>
<p>VARCHAR</p>
<p>VARCHAR'('INTNUM')'</p>
<p>NVARCHAR</p>
<p>NVARCHAR'('INTNUM')'</p>
<p>CHAR'('INTNUM')'</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">NUMERIC:</td>
<td>
<p>NUMERIC</p>
<p>NUMERIC'('INTNUM')'</p>
<p>NUMERIC'('INTNUM','INTNUM')'</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">DECIMAL:</td>
<td>
<p>DECIMAL</p>
<p>DECIMAL'('INTNUM')'</p>
<p>DECIMAL'('INTNUM','INTNUM')'</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">INTEGER:</td>
<td>
<p>INT</p>
<p>INTEGER</p>
<p>SMALLINT</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">FLOAT:</td>
<td>
<p>FLOAT</p>
<p>FLOAT'('INTNUM')'</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">REAL:</td>
<td>
<p>REAL</p>
<p>DOUBLE PRECISION</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">LONG VARCHAR:</td>
<td>
<p>BLOB data</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">VARBINARY [( precision )]:</td>
<td>
<p>Binary data</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">LONG VARBINARY:</td>
<td>
<p>Binary BLOB data</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">TIMESTAMP:</td>
<td>
<p>TIMESTAMP</p>
<p>DATETIME</p>
<p>TIME</p>
<p>DATE</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap"><UDT>:</td>
<td>
<p>User Defined Type with varbinary-like size properties.</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">LONG <UDT>:</td>
<td>
<p>User Defined Type with LONG varbinary-like size properties.</p>
</td>
</tr>
<tr>
<td align="right" valign="top" class="varterm" nowrap="nowrap">LONG XML:</td>
<td>
<p>LONG XML BLOB-like data type.</p>
</td>
</tr>
</table>
<div class="note">
<div class="notetitle">Note:</div>
<p>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.</p>
</div>
<a name="dt_dateliterals" />
<h3>8.1.1. Date Literals</h3>
<p>
Virtuoso does not support date literals or the
DATE reserved keyword. Literal dates should be
enclosed in a conversion function such as
<span class="computeroutput">stringdate()</span>, as in this example:
</p>
<div>
<pre class="programlisting">
select * from demo.demo.orders o
where o.orderdate between stringdate('1994-01-01') And stringdate('1997-12-31')
</pre>
</div>
<p>Alternatively type casts can be used to explicitly instruct Virtuoso
to assume a string as a date, see below.</p>
<br />
<a name="dtcasting" />
<h3>8.1.2. Casting</h3>
<p>
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.
</p>
<p>
Nothing can be cast to a blob type. Blobs only come from selecting
blob columns and are created by assigning
values to such columns.
</p>
<p>
Converting non-integer values to integers rounds towards 0.
</p>
<p>
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.
</p>
<div>
<pre class="programlisting">
cast_expression :
CAST '(' scalar_exp AS data_type [COLLATE collation_name ] ')'
collation_name :
identifier
| owner '.' identifier
| qualifier '.' owner '.' identifier
</pre>
</div>
<p>
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.
</p>
<p>
CAST is the recommended way of converting between data types,
including any conversion between strings, different number types and datetime types.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
select cast ('2000-1-3' as date);
select cast (cast ('2000-1-3' as date) as varchar);
= 2000-01-03 00-00-00 000000
</pre>
</div>
</div>
<br />
<a name="sqldatemanus" />
<h3>8.1.3. Time & Date Manipulation</h3>
<p>The SQL92 standard functions for time and date queries are available.
These are mapped to Virtuoso internal functions as follows:</p>
<ul>
<li>
<strong>CURRENT_DATE</strong> - <a href="fn_curdate.html">curdate()</a>
</li>
<li>
<strong>CURRENT_TIME</strong> - <a href="fn_curdate.html">curtime()</a>
</li>
<li>
<strong>CURRENT_TIMESTAMP</strong> - <a href="fn_curdate.html">curdatetime()</a>
</li>
</ul>
<p>The results of the above functions can also be obtained using the
<a href="fn_now.html">now()</a> function and
casting to the appropriate target type.</p>
<p>Dates and times should be input or compared as literals in a standard
format. The following table describes the proper methods available:</p>
<table class="data">
<caption>Table: 8.1.3.1. Time & date syntax</caption>
<tr>
<th class="data">Datatype</th>
<th class="data">ODBC Syntax</th>
<th class="data">SQL92 Casting</th>
</tr>
<tr>
<td class="data">Date</td>
<td class="data">{d 'yyyy-mm-dd'}</td>
<td class="data">cast('yyyy-mm-dd' as date)</td>
</tr>
<tr>
<td class="data">Time</td>
<td class="data">{t 'hh:mm:ss'}</td>
<td class="data">cast('hh:mm:ss' as time)</td>
</tr>
<tr>
<td class="data">Datetime/timestamp</td>
<td class="data">{ts 'yyyy-mm-dd hh:mm:ss[.f...]'}</td>
<td class="data">cast('yyyy-mm-dd hh:mm:ss[.f...]' as datetime)</td>
</tr>
</table>
<br />
<a name="ex_comparingdates" />
<div class="example">
<div class="exampletitle">Example: comparing dates</div>
<div>
<pre class="programlisting">
select datecol from table
where datecol > cast('1900-01-01' as date)
and datecol < cast(now() as date);
</pre>
</div>
<p>now() is cast for explicit compatibility although not required.</p>
</div>
<br />
<a name="deccolexp" />
<h3>8.1.4. Declaring Collations of Expressions</h3>
<p>
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:
</p>
<div>
<pre class="programlisting">
CAST 'foo' as varchar collate insensitive_ascii) = CAST 'FOO as varchar collate insensitive_ascii)
</pre>
</div>
<p>
would be true.
</p>
<p>
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.
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="sqlreference.html" title="SQL Reference">Previous</a>
<br />Contents of SQL Reference</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="udt.html" title="User Defined Types">Next</a>
<br />User Defined Types</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
884
