compare 2 dates in different formats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to do a datedif for 2 date/times, one has the year in it and one
doesn't. I'd like to only select records where the date/times are 2 minutes
or less different. I know how to do this if I could get rid of the year in
one date field. Any suggestions?
 
What do you mean by one has the year in it and one doesn't? Are these date
fields or text fields?
You may be able to use the TimeValue function to strip out the time only
from the dates.

If these are text fields, I suggest you post a couple of examples and the
expected results.
 
I want to do a datedif for 2 date/times, one has the year in it and one
doesn't. I'd like to only select records where the date/times are 2 minutes
or less different. I know how to do this if I could get rid of the year in
one date field. Any suggestions?
Click to expand...

The Format of a date/time field merely controls how it is displayed -
not what's stored in the table. It is IMPOSSIBLE to have a date/time
field without a year. Internally, a date is stored as a double float
number, a count of days and fractions of a day since midnight,
December 30, 1899. If you enter a date using only the day and the
month, Access will automatically fill in the current year - so you may
have records in the table with a WRONG year, or this year.

Try changing the Format property of the form or report textbox - or,
if you wish, the query or table - to mm/dd/yyyy. If the field is in
fact a date/time field you'll see a year; and you can decide where to
go based on what year you see.

If you JUST want to ignore the year altogether in both fields (so that
the time difference between #11/15/2005 14:30:00# and #11/15/1724
14:31:59# comes out to 119 seconds) try using the Timevalue function
to strip off the date portion entirely (both times will be on December
30, 1899):

DateDiff("s", TimeValue([date1]), TimeValue([Date2]))

Or, more complicated but handling time across midnight and times that
are close on the clock but separated on the calendar:

DateDiff("s", DateSerial(2000, Month([Date1]), Day([Date1]) +
TimeValue([date1]), DateSerial(2000, Month([Date2]), Day([Date2]) +
TimeValue([date2])


John W. Vinson[MVP]
 
CherylfromMSU said:
I want to do a datedif for 2 date/times, one has the year in it and
one doesn't. I'd like to only select records where the date/times
are 2 minutes or less different. I know how to do this if I could
get rid of the year in one date field. Any suggestions?
Click to expand...

If it doesn't have a year in it then it is not a DateTime. DateTimes are
ALWAYS a complete value with year, month, day, hours, minutes and seconds.
The fact that you might be using a format that supresses the display of any
of these components does not mean that they are not there.

Can you elaborate exactly what kind of data you are dealing with?
 
Back
Top