It is up to the
developer to determine and control whether a time value is included. That is
why there are three different functions.
Date() returns only the date portion.
Now() returns date and time.
Time returns only the time.
No!
Standard SQL has DATE and TIME date types, plus some others, but
Access/Jet has but one temporal data type, named DATETIME (close to
Standard SQL's TIMESTAMP data type but nothing like SQL Server's). The
clue's in the name...
In Access/Jet, Date() always returns a time portion, being midnight;
Time() always returns a date portion, being of 30th of December
eighteen hundred and ninety-nine. You can hide certain date/time
elements using formatting but that doesn't make the underlying values
disappear. This is newbie stuff and you know it
All Date values in Access are not granular to 1 second.
I guess I was summarizing my conclusions based on experience rather
than stating a solid fact; sorry for not being clear. Time is in
continuum, periods can be infinitely divided and in this regard the
floating point nature of Access/Jet's DATETIME functionality is a good
fit; pity it's not practical. Unless prevented, someone can put sub-
second values in a DATETIME column but to operate on it would have to
roll their own temporal functionality (non-trivial to say the least)
because the smallest useable time granule in Access/Jet's own is one
second. If you take the aforementioned prevention approach it gets to
be a pain too, not so bad for table columns where engine-level
validation rules can be used but really onerous for input parameter
values (I shudder to contemplate the consequence if you're a 'dynamic
SQL' person).
Similarly, someone could roll their own fixed point temporal data
type; this is not so fanciful: we see many people here trying to use
text data types to achieve the same and the result usually a horrible
mess.
Of course, most designers use DATETIME, take no action at all but
assume all dates will be of one day granularity, and we see many of
them in the groups wondering out loud why their rows disappear when
JOINed on DATETIME columns and the answer is they allowed time
elements other than midnight and consequently got them.
My conclusions are to use DATETIME for instants, a pair of DATETIMEs
for periods,use engine-level validation rules to ensure DATETIME
columns' values are of known granularity no smaller than one second
(but often larger granules) and either 'round' parameter values to the
same granularity of the column they are being compared while allowing
sub-second values to be implicitly rounded (fine by me: it's not my
fault that DATETIME is not based on fixed point so I shouldn't be
expected to fix it!) I can then used closed-closed representation for
periods e.g. period for the current month would be represented as
[#2007-08-01 00:00:00#, #2007-08-31 23:59:59#]
The OP is also using closed-closed but would represent the same period
as
[#2007-08-01 00:00:00#, #2007-08-31 00:00:00#]
which begs the question, where does the value #2007-08-31 00:00:00#
fall? No where probably.
Now you may ask where does the value 39325.999994213 fall in my
version and I tell you that 39325.999994213 is not a valid DATETIME
value (!!) and if the front end designer or user allowed it to be used
as if it were on then that's their problem (of course I could ensure
the values were always rounded but it would result in a complex and
less flexible system -- and I repeat I shouldn't be expected to fix
the DATETIME data type's floating point 'problem'). I don't think the
OP could use the same defence because valid DATETIME values other than
midnight are far more commonly encountered and there no real excuse
for not anticipating them e.g. as a I suggested they could make their
end DATETIMEs have time element one second before midnight OR use
closed-open representation e.g.
[#2007-08-01 00:00:00#, #2007-09-01 00:00:00#)
and how hard is that said:
What is not clear here is which date to use for the week. There are two
dates, but the OP did not say which should be used to return the week. In
the example, the start date is week 1, but the end date is certainly not.
Additionally, is there any reason the DatePart function can't be used to
determine the week rather than having to maintain a table? It would be
faster.
=DatePart("ww",SomeDate)
Is there any reason the DatePart function can't be used? You seem to
have answered your own question: using DatePart is fair enough when
your definition of week start date and week end date happens to
coincide with Microsoft's; if not, you've got to roll your own.
SQL (the language) was designed for data storage and data retrieval
and not surprisingly it excels in this area. It was not designed as a
calculation engine, hence doesn't do so well in this area. It stands
to reason to prefer a data-driven solution, using data stored in
permanent tables joining to other tables etc, over a calculation.
Putting the logic into a UDF, for example, obscures the logic of the
application and can only run inside the Access user interface, whereas
a calendar table style solution is clear and available to all. It also
has a many uses e.g. finding the number of enterprise days between two
dates; identifying overlapping periods (JOIN to the calendar table and
GROUP BY calendar day), etc.
Maintenance isn't usually a problem because the calendar tends to be
quite stable <g>! Also consider it's easier to change data in a table
than it is to make a code change to a UDF. Further, the table-driven
approach ports well to other SQL DBMSs.
Jamie.
