Working with Dates (converting text to datetime and date calculations)

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

Guest

1. How do I convert a Text field that has records like '2002-07-17 21:39:08.392325' to a Datetime field in Access. (I really don't need the millisecond part and would not mind getting rid of that if possible)
2. I want to do a DateDiff between two date fields however, I want to "only" count times that fall within 6AM - 11PM. In other words, if the datetime on one field is '2002-10-28 22:59:00.000000' and '2002-10-29 06:02:00.000000' on the other, the result of a datediff would be 3 minutes.

Thank you so much for your help.
 
1) The CDate function should be able to convert that string without any
problem (and don't worry about the milliseconds: VBA dates don't support
them anyhow)

2) Why would that be 3 minutes? 22:59 doesn't fall within the range 6AM -
11PM. In any case, you're going to have to write your own function to do
this. Figure out whether the starting datetime is within the range (you can
use TimeValue to strip the time portion from the datetime). If it isn't,
figure out the appropriate starting datetime to use. (If the time is after
11PM but before midnight, you want to start at 6AM the next day. If it's
after midnight but before 6AM, you want to start at 6AM that same day)
Figure out whether stopping datetime is within the range, and figure out the
appropriate stopping datetime to use if it isn't. (If the time is after 11PM
but before midnight, you want to stop at 11PM the same day. If it's after
midnight but before 6AM, you want to stop at 11PM the previous day) Make
sure that the difference between starting and stopping doesn't cross an
undesirable range. If it does, subtract 7 hours for each day.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



New2Access said:
1. How do I convert a Text field that has records like '2002-07-17
Click to expand...
21:39:08.392325' to a Datetime field in Access. (I really don't need the
millisecond part and would not mind getting rid of that if possible)
2. I want to do a DateDiff between two date fields however, I want to
Click to expand...
"only" count times that fall within 6AM - 11PM. In other words, if the
datetime on one field is '2002-10-28 22:59:00.000000' and '2002-10-29
06:02:00.000000' on the other, the result of a datediff would be 3 minutes.
 
Back
Top