|
The title of this week's tip may seem a little obvious at first, but some of the consequences of having a data type that stores both date and time are not so obvious.
Suppose I have a table of motor vehicle accidents:
Table: Accident
AccidentID int identity primary key
When datetime
Summary varchar(100)
On my search screen, I have the option of searching for accidents which occurred within a given date range.
So if I ask for a list of all the accidents which occurred between 10 May 2001 and 25 May 2001, I want a piece of SQL that looks like:
SELECT Summary FROM Accident
WHERE When>=#05/10/2001# AND When<=#05/25/2001#
(I'm using Access for this example, which uses the # to denote literal date values, at least if you are using a US keyboard).
OK, but what about an accident that occurred at 5am on 25 May, 2001?
A date value by itself is equivalent to midnight on that date. So the query above is equivalent to:
SELECT Summary FROM Accident
WHERE When>=#05/10/2001 0:00# AND When<=#05/25/2001 0:00#
This can lead to some really subtle errors, particularly if during data entry the time can be left blank on some records and not others. That would mean that my query could even pick up *some* of the accidents that occurred on May 25, but not any of the accidents which have a time recorded.
My preferred way around this is to look for all of the accidents which occurred up to (but not including) midnight the following night:
SELECT Summary FROM Accident
WHERE When>=#05/10/2001 0:00# AND When<#05/26/2001 0:00#
In VB, the code to generate this SQL would look something like
sSql = "SELECT Summary FROM Accident" _
& "WHERE When>=#" & format$(CDate(txtDateFrom.text),"mm/dd/yyyy") & "#" _
& " AND When<#" & format$(CDate(txtDateTo.text)+1,"mm/dd/yyyy") & "#"
|