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.