Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subtracting two dates/times

Status
Not open for further replies.
Crevalle

Crevalle

Technical User
Aug 16, 2004
36
0
0
US
I need to subtract one date from another in order to find the time difference between the two (to the hour). The date fields are each in a separate table. The data is in "text" format, and looks like the following:

2001-08-01-10.03.04.000000 (this is 08/01/2001 10am, I think)

I would like to subtract two dates, and get a number like 12.72 (days). This obviously means I will only need year/mo/day/hours.

I've been trying to use an update query to change the existing field format to 13 digits with this:

Format(Left([TCR_R_INQ_STATUS].[UPDATE_DATE],13),"mm/dd/yyyy hh")

It does change the date to 13 digits, but when I try to subtract the dates, I get "#error" messages in the field.

Any help would be greatly appreciated. I've been trying to figure this out for hours.
 
Sort by date Sort by votes
try dateadd function. You should be able to look it up in help. Not sure though if it will do exactly what you are looking for. You may have to do some extra calcs besides.
 
Upvote 0 Downvote
I'm not advanced enough to use SQL or anything like that. I looked at the dateadd function, but it didn't do what I need.
 
Upvote 0 Downvote
You might want to try an extremly simple expression:

[UPDATE_DATE]-[other_date]

with the query field format set to "Fixed".

If you use "DateDiff", choose "h" as unit and divide by 24, like

DateDiff("h",[other_date],[UPDATE_DATE])/24

to get the fraction value.

TomCologne

 
Upvote 0 Downvote
2001-08-01-10.03.04.000000 is not in a format that Access understands, unless it is one of the foreign settings. Anyway, what you will need to do is split the time from the date and do the calcs seperately or somehow get the date/time into a format that can be used. But ultimately, you will want to use the dateadd function.

If you can get the above date/time to look more like this:

08/01/2001 10:03:04:000000

you can do the calculation by using the dateadd function like this:

Code: 
dateadd("hh", date1, date2)

I am assuming you are placing this in a query or form somewhere. date1 is the date to be subtracted and date2 is the later date you are subtracting from.

As far as getting the date/time into the correct format, you will need to use the mid function or perhaps someone will come up with an easier way to do it.
 
Upvote 0 Downvote
converting that particular character string format to a date value is easy, just grab the date portion (i.e. strip the time portion off):
Code: 
select dateadd("d",0,left(UPDATE_DATE,10)) 
            as sameday

this is because the left 10 characters are in standard ISO date format, which access supports

then just use the DATEDIFF function on the two date values

rudy
SQL Consulting
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

serino
  • Question
Find a number between two sets of numbers. 1
Replies
2
Views
301
serino
serino
spartansFC
  • Locked
  • Question
Display Year between 2 dates 1
Replies
1
Views
123
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Use two sorts in query 1
Replies
7
Views
66
TrekBiker
TrekBiker
Wolfdzn
  • Locked
  • Question
Join two seperate SQL into one
Replies
6
Views
81
dhookom
dhookom
serino
  • Locked
  • Question
Adding Records between two ranges 2
Replies
12
Views
152
serino
serino

Part and Inventory Search

Sponsor

Back
Top