Difference between dates

  • Thread starter Thread starter GregB
  • Start date Start date
G

GregB

I have an inventory system database. invoices are ordered and have a date
associated with them (date1). When the invoices come in, they are scanned in
and another date is associated with them (date2)

THe dates are assoicated by an invoice number.

First I would like to run a query on the difference of the dates that
displays the invoice number and the difference between date1 and date2 if the
difference is more than x

Secondly, I would like to run a query that callaculates the difference of
the dates and how many occured for the year

192 5 days
102 4 days
148 3 days
300 2 days etc.

Thank you very much for taking your time to help!
 
hi Greg,
First I would like to run a query on the difference of the dates that
displays the invoice number and the difference between date1 and date2 if the
difference is more than x
Take a look at the DateDiff() function.

mfG
--> stefan <--
 
Thanks, I tried it and it works but when I try to run the query with
critera, using the between date And date, on date1 to get the difference for
the dates for the year i get blank results


I GROUP by the DateDiff statment
and then COUNT invoices
and that works fine

But when I add in Date1 and set it to a where, using the Between date And
date, it returns blank results

What am I doing wrong??

Thanks for the help
 
Greg,

As Stefan has stated, the DateDiff function is what you need. More
specifically, the queries you want are as follows:

To show the invoice # and the difference between the two dates:
SELECT InvoiceNumber, Date2-Date1 As DifferenceInDays
FROM MyTable

To show how many of each difference occurred within the current year.
SELECT Count(*) As HowMany, (Date2-Date1) As DifferenceInDays
FROM MyTable
WHERE (Date2-Date1) > 0
GROUP BY (Date2-Date1)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Er, sorry, I was going to use DateDiff, but then decided against it. I
figured simple arithmetic would be an easier solution.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
If the year is a calendar year you can simply enter the year, e.g. as a
parameter so that you are prompted for the year at runtime:

PARAMETERS [Enter Year:] SHORT;
SELECT YEAR(Date1) As InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
FROM Invoices
WHERE YEAR(Date1) = [Enter year:]
AND Date2 IS NOT NULL
GROUP BY YEAR(Date1), DATEDIFF("d",Date1,Date2);

Not that testing for Date2 IS NOT NULL will exclude any rows where Date2 has
not yet been entered. If such rows were included you'd get an extra row
returned with Null duration (anything minus Null = Null) and the count of the
rows in question.

If the year is a fiscal or accounting year for which you want to specify
start and end dates:

PARAMETERS [Start date:] DATETIME,
[End date:] DATETIME;
SELECT "Year from " & [Start date:] & " to " & [End date:]
As InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
FROM Invoices
WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL
GROUP BY DATEDIFF("d",Date1,Date2);

Note that particularly with parameters of date/time data type its a good
idea to declare them as such; otherwise a date entered in short date format
could be interpreted by Access as an arithmetical expression rather than a
date, and give the wrong results. This might possibly explain why you are
getting no rows returned.

Ken Sheridan
Stafford, England
 
Graham:

True, but I was justifiably taken to task here recently for suggesting the
use of simple date arithmetic and consequently not following my own advice of
avoiding 'undue chumminess with the implementation'. While date arithmetic
is not in the same league of chumminess as relying on the implementation of
Boolean values in Access as 0 or -1 (which is where I've frequently cautioned
against it), it has to be admitted that it is reliance on the implementation,
and as such is, as my recent nemesis maintained, probably best avoided in
favour of calling the DateDiff function.

Ken Sheridan
Stafford, England
 
thanks for the responces guys.

Graham, I didn't even think of the way you approached it, but when I use
your code
SELECT InvoiceNumber, Date2-Date1 As DifferenceInDays
FROM MyTable

when i run the query i get an #error message in all of the DifferenceInDays
column

Ken, can you explain your code to me, i do understand basic sql statements
but i havn't used one with paramaters

PARAMETERS [Start date:] DATETIME,
[End date:] DATETIME;
SELECT "Year from " & [Start date:] & " to " & [End date:]
As InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
FROM Invoices
WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL
GROUP BY DATEDIFF("d",Date1,Date2);


*Do i change all the [Start date:] and [End date:] to the years i want
or do i change DATETIME to the years I want in the paramters (or is that
stating the format), if so is there a certain format.. 02/01/08 etc

Also can someone explain to me, so that I am actually learning and don't
have to post everytime I have a question :) why i couldn't use the Between
function?

Thanks again.. you guys are awsome!
 
Firstly you don't have to change [Start date:] and [End date:] to actual
dates. These are the parameters. A parameter in a query accepts a value
from outside the query when its run (or indeed when a form or report based on
the query is opened). With a simple parameter like these Access pops up a
dialogue box for each prompting you to enter the dates. This works fine but
a more developed approach is to reference controls on a form as the
parameters; this means you'd open the form, enter the two dates in text boxes
or select them from combo boxes which list the relevant dates, and then open
the query (or better still a form or report based on the query) with a button
on the form. In a scenario like this the parameters would then take the form
of Forms!frmDatesDlg!txtStartdate and Forms!frmDatesDlg!txtEndDate.

Lets break the query down:

PARAMETERS [Start date:] DATETIME,
End date:] DATETIME;

This declares the two parameters as date/time data type, so when you enter a
date at the prompt Access knows it’s a date you are entering. A date such as
02/01/08 might otherwise be interpreted as an arithmetical expression 2
divided by 1 divided by 8, which is of course 0.25. Access implements dates
as a 64 bit floating point number with the integer part representing days and
the fractional part the time of day. Zero is 30 December 1899 00:00:00 (its
important to understand that date time values are always a point in time, so
there is no such thing as date value per se, rather one with a zero time of
day, i.e. midnight at the start of the day. So 0.25 is one quarter way into
day zero, i.e. 30 December 1899 06:00:00. Your query is unlikely to find
such a date time value in any row, I'd imagine!

Note how the individual parameter declarations are delimited by commas and
the PARAMETERS clause is terminated by a semi-colon.

SELECT "Year from " & [Start date:] & " to " & [End date:]
AS InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences

This is the query's SELECT clause and determines which columns it returns.
The first column simply concatenates a couple of literal strings with the
dates you enter as the parameters at runtime, so it tells you what date range
is being looked at. The 'AS InvoiceYear' just gives this column a name.
Remember that if you use spaces or other special characters in object names,
however, you'll need to wrap them in brackets, e.g. [Invoice Year].

The second column subtracts the date time values in the two columns using
the DateDiff function (SQL keywords, function names etc are usually written
in upper case in SQL, with table and column names in lower or mixed case ( I
tend to use CamelCase where the words are joined up with the start of each
one capitalised, though some SQL programmers favour proper case for table
names and all lower case for column names).

The third column just counts the rows. Using the asterisk character counts
all rows in each group (determined by the GROUP BY clause, about which more
below).

FROM Invoices

This simply means the query uses a table called Invoices. As for this
example I've assumed only one table is being used the table is not joined to
any other(s) in a JOIN clause.

WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL

The WHERE clause restricts the query to those rows where Date1 is between
(inclusively) the two dates entered as the parameter values, and where Date2
contains a date (IS NOT NULL). A WHERE clause restricts the data before
grouping it, i.e. only the restricted data is grouped and counted. In query
design view you'll sometimes include a column in a GROUP BY clause and put a
criterion in the same column in the design grid. This will give you a HAVING
clause not a WHERE clause. A HAVING clause restricts the data after grouping
it. People often create a HAVING clause incorrectly in this way when they
should create a WHERE clause. To do the latter in design view you add the
column a second time to the grid, select 'where' in the 'totals' row and
uncheck the 'show' checkbox. The legitimate use of a HAVING clause usually
acts on an aggregated value, e.g. you might want to restrict the results to
only those rows where the difference between the dates is more than 5 days,
for which the HAVING clause would be:

HAVING COUNT(*) > 5

A HAVING clause follows the GROUP BY clause, whereas a WHERE clause precedes
it.

GROUP BY DATEDIFF("d",Date1,Date2);

The GROUP BY clause groups the query's results by the difference between the
two dates, so it counts the rows for each distinct number of days returned in
the computed InvoiceDuration column. The semi colon terminates the SQL
statement.

Finally I see absolutely no reason why you can't use a BETWEEN….AND
operation, as I've done in my example. One thing to note about a
BETWEEN….AND operation, however, is that if any rows have dates on the final
days of the range, but with a non-zero time of day (which might not be
readily apparent due to the formatting of the column) those rows won't be
returned as if the final day is 31 March 2008 say, then only rows with a
value at midnight at the start of that day will be returned; anything on that
day with a non-zero time of day is outside the range. To prevent this
include a validation rule for each of the date columns in table design of:

DateValue([Date1])

and

DateValue([Date2])

respectively.

Before doing this you can correct any existing errant dates with update
queries:

UPDATE Invoices
SET Date1 = DATEVALUE(Date1)
WHERE Date1 IS NOT NULL;

and:

UPDATE Invoices
SET Date2 = DATEVALUE(Date2)
WHERE Date2 IS NOT NULL;

Ken Sheridan
Stafford, England

GregB said:
Ken, can you explain your code to me, i do understand basic sql statements
but i havn't used one with paramaters

PARAMETERS [Start date:] DATETIME,
[End date:] DATETIME;
SELECT "Year from " & [Start date:] & " to " & [End date:]
As InvoiceYear,
DATEDIFF("d",Date1, Date2) AS InvoiceDuration,
COUNT (*) As Occurrences
FROM Invoices
WHERE Date1 BETWEEN [Start date:] AND [End date:]
AND Date2 IS NOT NULL
GROUP BY DATEDIFF("d",Date1,Date2);


*Do i change all the [Start date:] and [End date:] to the years i want
or do i change DATETIME to the years I want in the paramters (or is that
stating the format), if so is there a certain format.. 02/01/08 etc

Also can someone explain to me, so that I am actually learning and don't
have to post everytime I have a question :) why i couldn't use the Between
function?

Thanks again.. you guys are awsome!
 
Greg,

Any code we give you here are examples only. You must change the field names
"InvoiceNumber", "Date1" & "Date2" to whatever your fields are called. Then
it will work.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Ken,

I'm sorry if you were admonished for something, but since I was not aware of
the actual reason for your "admonishment", my guess is the scenario may have
warranted it. Nonetheless, my solution will work, and it will work reliably,
so I stand by it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
The point James Fortune (I think it was he) was making when he rebuked me was
that simple date arithmetic per se is a reliance on the implementation of the
date/time data type in Access as a 64 bit floating point number. The context
in which I'd suggested it is irrelevant to whether it was warranted or not
therefore, and I don't recall now exactly what the context was. The reason
James took me to task was that I've regularly objected to the use of
expressions like Sum(Abs([MyBooleanColumn])) or Sum([MyBooleanColumn]*-1) to
count the instances of True values, because this relies on the implementation
of Boolean False and True values as 0 and -1. I can recall many others far
more experienced than me having made the same point over the years. The
reason is of course that one cannot assume the implementation will remain
unchanged. A Boolean True is 1 in VB Net as far as I recall, whereas it is
-1 in VB 6 and earlier, and while the former was really a new language, not a
later version of the latter, it does illustrate that the implementation is
not immutably written on tablets of stone.

What James was essentially saying was that the use of simple date arithmetic
is just as much a reliance on the implementation as the more overtly
inadvisable reliance on it in the case of Boolean values, so in his view I
was being inconsistent in suggesting the former while raising objections to
the latter. I fully accept that he has a valid and irrefutable point, and
while many of us have been in the habit of using simple date arithmetic
rather than the DateDiff function, we perhaps should be prepared to
acknowledge that we are departing from best practice by doing so. Of course
it usually works, has done so since before Doris Day was a virgin, and might
well continue to do so until the end of time, but there can be no absolute
guarantee that the implementation won't change and it will continue to do so.

You'll probably have noticed the qualification of 'works' with 'usually'
above. In fact, when we examine this question from a practical point of view
rather than one of principle, it might not always do so. My guess would be
that less experienced Access users will rarely take steps in a table
definition to prevent a Date/Time column including non-zero times of day even
when only dates need to be recorded. If a non-zero time off day
inadvertently creeps in (as Murphy's Law states will happen sooner or later)
then GROUP BY (Date2-Date1) clause would not group the result table by
distinct numbers of days, whereas GROUP BY DATEDIFF("d",Date1,Date2) would
because the times of day are irrelevant to the latter. It might be thought
that GROUP BY INT(Date2-Date1) would be reliable, but that's not the case as
Date2 might include a later time of day than Date1. You could use GROUP BY
INT(Date2)-INT(Date1), or GROUP BY DATEVALUE(Date2)-DATEVALUE(Date1), but
these can not really be called 'simple arithmetic' any longer.

BTW the reference to being 'unduly chummy with the implementation' is a
quote from the head of a software company, who was in the habit of saying
this if any of his staff did something which he regarded as an inadvisable
reliance on the implementation.

Ken Sheridan
Stafford, England
 
Hi Ken,

I'm afraid I don't know James Fortune. Sorry.

Although I agree with your (and James') argument (note that I didn't
disagree with it), the format of the date number is not likely to change on
the Windows OS any time soon, because that would quite literally break
everything that runs on the Windows platform. So although your argument is
sound, the specific arithmetic date operation I recommended here is safe for
the life of the OP's application without fear of it breaking. That is just
one reason why I suggested it in this case. In a different scenario, I may
suggest a different solution.

GROUP BY (Date2-Date1) will group as desired in the event that a zero time
component creeps in. That's how it was tested.

Date 2 being earlier than Date1 is always a danger, regardless of whether
one uses arithmetic or DateDiff().

By the way, unless we suddenly get another value for Boolean True,
Sum(Abs([MyBooleanColumn])) is not implementation-dependent, as it caters
for 1 and -1. And yes, I am aware of the difference between C++ True and VB
True.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Ken Sheridan said:
The point James Fortune (I think it was he) was making when he rebuked me
was
that simple date arithmetic per se is a reliance on the implementation of
the
date/time data type in Access as a 64 bit floating point number. The
context
in which I'd suggested it is irrelevant to whether it was warranted or not
therefore, and I don't recall now exactly what the context was. The
reason
James took me to task was that I've regularly objected to the use of
expressions like Sum(Abs([MyBooleanColumn])) or Sum([MyBooleanColumn]*-1)
to
count the instances of True values, because this relies on the
implementation
of Boolean False and True values as 0 and -1. I can recall many others
far
more experienced than me having made the same point over the years. The
reason is of course that one cannot assume the implementation will remain
unchanged. A Boolean True is 1 in VB Net as far as I recall, whereas it
is
-1 in VB 6 and earlier, and while the former was really a new language,
not a
later version of the latter, it does illustrate that the implementation is
not immutably written on tablets of stone.

What James was essentially saying was that the use of simple date
arithmetic
is just as much a reliance on the implementation as the more overtly
inadvisable reliance on it in the case of Boolean values, so in his view I
was being inconsistent in suggesting the former while raising objections
to
the latter. I fully accept that he has a valid and irrefutable point, and
while many of us have been in the habit of using simple date arithmetic
rather than the DateDiff function, we perhaps should be prepared to
acknowledge that we are departing from best practice by doing so. Of
course
it usually works, has done so since before Doris Day was a virgin, and
might
well continue to do so until the end of time, but there can be no absolute
guarantee that the implementation won't change and it will continue to do
so.

You'll probably have noticed the qualification of 'works' with 'usually'
above. In fact, when we examine this question from a practical point of
view
rather than one of principle, it might not always do so. My guess would
be
that less experienced Access users will rarely take steps in a table
definition to prevent a Date/Time column including non-zero times of day
even
when only dates need to be recorded. If a non-zero time off day
inadvertently creeps in (as Murphy's Law states will happen sooner or
later)
then GROUP BY (Date2-Date1) clause would not group the result table by
distinct numbers of days, whereas GROUP BY DATEDIFF("d",Date1,Date2)
would
because the times of day are irrelevant to the latter. It might be
thought
that GROUP BY INT(Date2-Date1) would be reliable, but that's not the case
as
Date2 might include a later time of day than Date1. You could use GROUP
BY
INT(Date2)-INT(Date1), or GROUP BY DATEVALUE(Date2)-DATEVALUE(Date1), but
these can not really be called 'simple arithmetic' any longer.

BTW the reference to being 'unduly chummy with the implementation' is a
quote from the head of a software company, who was in the habit of saying
this if any of his staff did something which he regarded as an inadvisable
reliance on the implementation.

Ken Sheridan
Stafford, England

Graham R Seach said:
Ken,

I'm sorry if you were admonished for something, but since I was not aware
of
the actual reason for your "admonishment", my guess is the scenario may
have
warranted it. Nonetheless, my solution will work, and it will work
reliably,
so I stand by it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top