In addition to the
standard SQLite SQL functions that you
can use in any expression SQLite3 COM
implements a number of functions such as OLE date/time and Session
parameters. Contents:
Session parameter functions
OLE date/time functions
Session parameter functions
The session parameter functions work together with the Parameters
collection maintained by the SQLite3 COM object. No matter if the
functions are called explicitly or implicitly they return the value
corresponding to the parameter in the same collection. By explicit use
we mean usage in a query passed to an Execute
method, by implicit usage we mean a call to these functions caused by
invoking a view or a trigger in result of execution of a query that
refers to the view or triggers the trigger.
The functions:
Parameter('<param_name>')
Returns the value of a parameter in the Parameters
collection. <param_name> is the name of the parameter in the
Parameters collection as set when the Add method or
object.Parameters(<param_name>) = soemthing has been used. If
an object is set under that name in the Parameters collection its
default property is returned. If the object has no such property
database error will occur. If the parameter does not exist "Parameter not found"
database error will occur.
RefDate() and RefDateSys()
Return the current date/time or the reference date/time as specified
in the Parameters object (see
the ReferenceDate and UseReferenceDate properties). The difference
between the two function is as follows: RefDate function returns the
current local date/time when Parameters.UseReferenceDate = false,
while RefDateSys returns the UTC date/time. Both functions return
the reference date "as is" if Parameters.UseReferenceDate
= true.
CallObject('param_name' [,'method_or_prop_name'
[, arg1 [, arg2 [ ...] ]]]
If the parameter in the Parameters collection specified by the name param_name
is an object this function calls a method on it or gets the value of
objects property. All the arguments except the param_name are
optional. If all are omitted the default property is returned (if
exists).
If a method_or_prop_name is specified the specified
property or method is called on the object.
All the remaining arguments are passed to the called method or
property. Their number and type must match the arguments of the
called method or property.
If an error occurs in the called method/property "Object error"
database error will occur.
If the param_name points to a parameter that is not an object "The specified parameter is NULL or not an object"
database error is issued.
The called method or property must return result which can be
converted to one of the database types INTEGER, REAL, TEXT, BLOB or
NULL. I.e. if the result is an object (without default property
returning scalar value) an error will occur: "Cannot convert/obtain the returned result in useful form."
OLE date/time functions
SQLite3 COM defines a set
of Date/Time SQL functions which can be used in SQL statements to deal
with date and time values in a manner compatible and convenient for
COM programming. They allow you work with date/time values even
without support from the outside - for instance making conversion
every time you read/write values from to the database. You can move
most of the date/time related work to the SQL instead of doing it in
the application code (VBScript, JScript, VB etc.). This is often more
productive and especially useful if embedding date/time calculations
in the SQL will offer more simplicity and better performance. For
instance if you need to filter some records based on some date/time
criteria that involves calculation of intervals for example, instead
of feeding the SQL with pre-calculated values it is better to do this
in the SQL statement in-place and thus benefit of the ability to
calculate them dynamically in the SQL over the current data. What
is the OLE DATE type? In short it is double precision floating
point value that counts the time from 30 December 1899 00:00:00. The
positive values mean date after this date, negative values mean date
before that date. Thus 0.0 will equal to 30 December 1899 00:00:00.
Therefore when OLE DATE is used to specify time only (without a date)
it will convert to 30 December 1899 plus some hours, minutes seconds
if it is converted to full date in a mistake. The OLE DATE values act
correctly in any expression because they are just real numbers, they
can be summed, subtracted and otherwise processed. The fact that the
time and the date are kept in a single value allows complex
calculations that involve date and time parts (and not only one of
them) to be performed easily. In contrast the Julian date supported by
the most databases (SQLite contains other set of functions for this)
keeps the date and the time in separate values and makes the
expressions more difficult to write. The additional benefit of using
OLE DATE is that the values that are result of expressions/statements
can be directly passed to any script or a COM routine that requires
date/time value without any conversion. The functions:
ParseOleDate - Parses a
date/time string in standard format and returns the double precision
value that represents it. The format is:
YYYY-MM-DD hh:mm:ss. Example:
SELECT * FROM T WHERE Created > ParseOleDate("2001-01-05");
will return the records with field "Created" containing
date bigger than or equal to January, 05, 2001.
SELECT * FROM T WHERE Created > ParseOleDate("2001-01-05
13:30");
will return the records with field "Created"
containing date/time bigger than or equal to January, 05, 2001 01:30
pm You can pass date only or time only to ParseOleDate function.
For instance all these:
ParseOleDate("2003-05-12")
ParseOleDate("05:15:00")
ParseOleDate("05:15")
ParseOleDate("2004-06-17 05:15:00")
will be ok. The seconds part of the time specification are optional. Note
that we are using this function in the samples below to make them
more readable. In the real world you will pass to them arguments
that are results from the query or an expression. OleDateTime
- Composes a date/time string in the standard format from a date
value. For instance PleDateTime(0.0) will return "1899-12-30
00:00:00". This function is needed when the date values are to
be converted to human readable format after some calculations. OleDate
and OleTime - Act as above but
return only the date part of the string representation (OleDate) or
only the time part (OleTime) of the date/time value passed as
argument. For example:
SELECT OleDate(ParseOleDate("2001-12-22 14:30:10"));
will return "2001-12-22"
SELECT OleTime(ParseOleDate("2001-12-22 14:30:10"));
will return "14:30:10"
OleDay, OleMonth,
OleYear, OleHour,
OleMinute, OleSecond
and OleWeekDay - all return
numeric value that represents the corresponding part of the date
value passed to them as argument. For example:
SELECT OleDay(ParseOleDate("2001-12-22 14:30:10"));
will return 22 (22 - day of the month)
SELECT OleMonth(ParseOleDate("2001-12-22 14:30:10"));
will return 12 (12 month - December)
SELECT OleYear(ParseOleDate("2001-12-22 14:30:10"));
will return 2001 (the year specified in the date
value)
SELECT OleHour(ParseOleDate("2001-12-22 14:30:10"));
will return 14 (2 p.m.)
SELECT OleMinute(ParseOleDate("2001-12-22 14:30:10"));
will return 30 (the minutes of the time contained
in the value)
SELECT OleSeconds(ParseOleDate("2001-12-22 14:30:10"));
will return 10 (the seconds of the time contained
in the value)
SELECT OleWeekDay(ParseOleDate("2001-12-22 14:30:10"));
will return 7 (Saturday) For example if you want
to query for records created on Mondays, assuming the Created field
contains their creation time you can use query like this:
SELECT * FROM T WHERE OleWeekDay(Created) = 7; The week
days are numbered as follows: 1 - Sunday, 2 - Monday ... 7 -
Saturday OleDateAdd - This
function provides way to calculate new date over existing one adding
an interval to it. The full specification of the function is:
OleDateAdd(interval,count,date)
interval - is a character which can be: "Y"
- years, "M" - months, "D" - days, "h"
- hours, "m" - minutes, "s" - seconds
count - is a number specifying how many interval-s
to add. Can be negative if you want to subtract from the date.
date - is the date value to which the interval will be
added.
For example this can be useful to fetch the records created in past
month:
SELECT * FROM T WHERE Created > OleDateAdd("M",-1,ParseOleDate("2004-12-14"))
AND Created < ParseOleDate("2004-12-14");
Assuming that the string in the ParseOleDate is passed from
outside. OleDateDiff - This function calculates the difference
between two date/time values in the interval-s specified. The full
specification is:
OleDateDiff(interval,date1,date2)
interval - One character specifying the interval in
which the difference will be calculated. Can be: "Y" -
years, "M" - months, "D" - days, "h" -
hours, "m" - minutes, "s" - seconds
date1 - The first date
date2 - The second date
If the date2 is bigger than date1 the result is positive (or 0) and
negative (or 0) otherwise.
For example if you want to fetch the records created this month you
can use query like this one:
SELECT * FROM T WHERE
OleDateDiff("M",Created,ParseOleDate("2004-12-14"))
= 0;
OleLocalTime(), OleSysTime()
- return the current local time (OleLocalTime) or the current UTC
time (OleSysTime).
A small sample ASP code.
These few lines of code Execute a query that retrieves the records
created during the previous year from a table "T", the field
"Created" is assumed to contain the record creation date. Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
dt = Now
Set r = db.Execute(su.Sprintf("SELECT * FROM T WHERE " & _
"OleDateDiff('Y',Created,ParseOleDate('%hT')) = 1",dt))
%>
<TABLE>
<%
For I = 1 To r.Count
%>
<TR>
<% For J = 1 To r(I).Count %>
<TD><%= r(I)(J) %></TD>
<% Next %>
</TR>
<%
Next
%>
</TABLE> |
