SQLite 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;
Why there is no Today or Now function? It is a common error
to use such function in the database. Note that between the composing
the query in the application and executing it in the database some
time will pass. Although it is insignificant in almost all the cases
it may be enough to cross a border of day month or even year. Thus
when composing queries that deal with date/time the current date/time
should be obtained once, just before starting to compose the query and
set in it from outside to ensure all the expressions in the SQL
statement and the external application code will use the same value.
Although such functions may be useful in some cases the mistakes they
may lead to convinced us to not include them in order to make
impossible mistakes like above. 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> |