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:
Table9.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) |
Example9.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.