Re: Parameter??

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


On Jan 8, 1:03 am, Smartin <smartin...@xxxxxxxxxxx> wrote:
Now you have two forms, one in which you can enter Budget and the other
for Current, for each month. You will also need to create a validation
rule or constraint of some kind to ensure the only dates that can be
entered are the first of a given month.

Good point.

Note that Access/Jet, in common with most (all?) available SQL
products, has only one temporal data type, being DATETIME (equivalent
to TIMESTAMP in the SQL-92 standard). A month is a period and periods
are modelled in SQL using a start date and an end date pair, both in
the same row because they are two elements of the same atomic fact.

Using the closed-closed representation, the current period's end date
will be one time granule before the immediately following period's end
date. Many people omit the end date, arguing that a subquery (expensive
in Access/Jet) can be used to reconstruct the periods. I counter that
if you must use a query is to reconstruct your data then it is
incorrectly modelled in the first place. Most common the closed-open
representation but I find closed-closed suits BETWEEN constructs i.e.

X BETWEEN Y AND Z

X>=Y AND X<=Z

the above are both equivalent but the first is IMO is more human
readable and the second requires two conditions to state one fact.

Out of interest, how do you propose implementing such a 'first of a
given month' Validation Rule or constraint ?

Let's consider a 'first of a given day' rule i.e. there are more days
than months, giving us more values to test performance!

I tested the following by loading my Calendar table, which holds one
row for each day between 1980-01-01 and 2010-12-31 inclusive (11323
rows):

CREATE TABLE Table1 (
date_col DATETIME NOT NULL,
CHECK(
date_col =
DATEADD('D',
DATEDIFF('D', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#)
)
);

The above logic is a little obscure for SQL DDL but I use the same
constructs in SQL DML, where it works well, and consistency in code is
a good thing, IMO. The logic for 'first of month' is similar i.e.
change granule type from 'D' to 'M'. Approx 270 milliseconds.

CREATE TABLE Table2 (
date_col DATETIME NOT NULL,
CHECK(
HOUR(date_col) = 0
AND MINUTE(date_col) = 0
AND SECOND(date_col) = 0
)
);

The above again uses DATETIME functions which commonly have equivalents
in other products and the logic is crystal clear but only if your are
aware that the smallest DATETIME granularity in Jet is one second.
Approx 230 milliseconds.

CREATE TABLE Table3 (
date_col DATETIME NOT NULL,
CHECK(
date_col = DATEVALUE(date_col)
)
);

The above uses a DATETIME function but one that is perhaps more
peculiar to Jet, hence has good portability is not as good. Approx 220
milliseconds.

CREATE TABLE Table4 (
date_col DATETIME NOT NULL,
CHECK(
date_col = INT(date_col)
)
);

The above relies on the Jet implementation to coerce a DATETIME to
DOUBLE FLOAT for a INTEGER comparison, for which you need to be very
familiar with Jet under the hood to follow the logic, plus it will not
port well. Approx 120 milliseconds.

Not surprisingly, the more you rely on the unique and physical
characteristics of the Jet implementation, the more obscure is the
logic and the more compromised is the portability but you will get
better performance in *relative* terms, noting that none of the above
examples perform badly in absolute terms.

Personally, I prefer clear logic in code with good portability so it
can be understood by a wide range of SQL coders (rather than, say,
Access power users only), which aids maintenance and means the code can
be moved to SQL platform (or to the same SQL platform in the unlikely
scenario where the Jet implementation moves away from epoch dates) with
minimum effort.

I cringe every time I read, "Dates are stored as floating point decimal
where the integer part represents the date and the decimal part
represents the time etc" (yes, I cringe many times each day <g>) as
someone explains the logic behind their proposed solution, usually
involving integer arithmetic on DATETIME values e.g. date_value + 1.
Surely logic such as DATEADD('D', 1, date_value) needs no explaining,
considering DATEADD can be found in the help?

Jamie.

--

.

Relevant Pages

  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ... The above relies on the Jet implementation to coerce a DATETIME to ...
    (microsoft.public.access.queries)
  • Re: Parameter??
    ... in common with most available SQL ... given month' Validation Rule or constraint? ... date_col DATETIME NOT NULL, ...
    (microsoft.public.access.queries)
  • Re: DTS Export to CSV
    ... Vyas, MVP (SQL Server) ... Production SQL Server - multi Zeon processor, 8GB ram, scsii 160 drives ... CONSTRAINT DEFAULT FOR ...
    (microsoft.public.sqlserver.programming)
  • Re: help with asp/sql convert data to date for comparison
    ... It must be MS SQL Server ... columns and store proper datetime values in a single column. ... Adjust that until you are creating proper datetimes. ... You should see data in the proper format. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Date: Can you have one that is just mm/dd or mm/yyyy etc?
    ... If so, they are right, just display it with less resolution might be your ticket. ... they were thinking of there was mixed resolutions in one DATETIME column. ... One thing to watch is to NOT take my suggestion and print out everything ... determine if a day exists in it, and build the SQL accordingly. ...
    (comp.lang.java.programmer)