OLE Date/Time SQL functions

SQLite DB Engine

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>