DateAdd Function in Queries

[Iftekhar]

I have the following installed on my PC:

OS Windows XP Home With SP2 (Fully Updated)
Access 2002 With SP3 (Fully Updated)

Regional & Language Options Short Date Format dd/MM/yy


Please consider the following queries:

Query 1:

SELECT TransDate, TransTime,
DateAdd('n', -1, TransTime) AS MinuteBefore,
DateAdd('n', 1, TransTime) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 11:58:00 PM 31/12/1899
31/01/06 12:00:00 AM 29/12/1899 11:59:00 PM 12:01:00 AM

MinuteAfter in the first record and MinuteBefore in the second record show
strange results.

Query 2:

SELECT TransDate, TransTime,
FormatDateTime(TransDate + DateAdd('n', -1, TransTime), 0) AS MinuteBefore,
FormatDateTime(TransDate + DateAdd('n', 1, TransTime), 0) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 30/01/06 11:58:00 PM 31/01/06
31/01/06 12:00:00 AM 29/01/06 12:01:00 AM 31/01/06 12:01:00 AM

MinuteBefore in the second record shows strange results.

tblDT contains only two fields i.e.
TransDate Type: Date/Time
TransTime Type: Date/Time

Nothing is set at table level.

Many thanks for reading. Any help / alternate would be appreciated.

 

[John Vinson]

On Sat, 4 Feb 2006 14:46:17 -0800, "Iftekhar"

Answers inline.

Please consider the following queries:

Query 1:

SELECT TransDate, TransTime,
DateAdd('n', -1, TransTime) AS MinuteBefore,
DateAdd('n', 1, TransTime) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 11:58:00 PM 31/12/1899
31/01/06 12:00:00 AM 29/12/1899 11:59:00 PM 12:01:00 AM

MinuteAfter in the first record and MinuteBefore in the second record show
strange results.

Perfectly normal results.

A Date/Time value in Access is stored as a Double Float number - a
count of days and fractions of a day (times) since midnight, December
30, 1899. A pure time value such as 11:59 pm is stored as a time on
that long-ago day. If you add one minute to that time, it becomes
equal to 1.0 - which is a pure date (no fractional part), one day
after the start point - that is, December 31, 1899. Similarly for the
subtraction, it becomes a time on the previous day.

Query 2:

SELECT TransDate, TransTime,
FormatDateTime(TransDate + DateAdd('n', -1, TransTime), 0) AS MinuteBefore,
FormatDateTime(TransDate + DateAdd('n', 1, TransTime), 0) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 30/01/06 11:58:00 PM 31/01/06
31/01/06 12:00:00 AM 29/01/06 12:01:00 AM 31/01/06 12:01:00 AM

MinuteBefore in the second record shows strange results.

Here you're doing something just plain wrong: adding a date field
(TransDate) to another date field (the result of the DateAdd
function). It will *work* but will give you unexpected results, just
as you see here!

tblDT contains only two fields i.e.
TransDate Type: Date/Time
TransTime Type: Date/Time

Nothing is set at table level.

Many thanks for reading. Any help / alternate would be appreciated.

Consider storing the transaction date/time in a single date/time
field. DateAdd("n", 1, #01/30/06 11:59:00pm#) will give you January 31
at midnight; adding another minute will give you 12:01am on that date.


John W. Vinson[MVP]

 

[Marshall Barton]

I have the following installed on my PC:

OS Windows XP Home With SP2 (Fully Updated)
Access 2002 With SP3 (Fully Updated)

Regional & Language Options Short Date Format dd/MM/yy


Please consider the following queries:

Query 1:

SELECT TransDate, TransTime,
DateAdd('n', -1, TransTime) AS MinuteBefore,
DateAdd('n', 1, TransTime) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 11:58:00 PM 31/12/1899
31/01/06 12:00:00 AM 29/12/1899 11:59:00 PM 12:01:00 AM

MinuteAfter in the first record and MinuteBefore in the second record show
strange results.

Query 2:

SELECT TransDate, TransTime,
FormatDateTime(TransDate + DateAdd('n', -1, TransTime), 0) AS MinuteBefore,
FormatDateTime(TransDate + DateAdd('n', 1, TransTime), 0) AS MinuteAfter
FROM tblDT;

Output From the Above Query:

TransDate TransTime MinuteBefore MinuteAfter
30/01/06 11:59:00 PM 30/01/06 11:58:00 PM 31/01/06
31/01/06 12:00:00 AM 29/01/06 12:01:00 AM 31/01/06 12:01:00 AM

MinuteBefore in the second record shows strange results.

tblDT contains only two fields i.e.
TransDate Type: Date/Time
TransTime Type: Date/Time

Exactly the expected result. The misunderstanding you are
dealing with is that a date/time variable/field contains
both a date part and a time part, not one or the other.

Note that the zero date is 30 Dec 1899 and a zero time is
midnight.

Using the default format: If the time part is zero, then
only the date part is displayed (as in both your first and
third results). If the date part is zero, it will display
as just the time part (your second result). You can get the
display you seem to want by setting the fields (or whatever
you are using to display the results) Format property to a
specific format such as hh:nn:ss

Bottom line is that you should only have one date/time field
in the table and use the Format property to specify how you
want to see the data.