Date & Time Difference

# Thread: Date & Time Difference

1. Ray
Senior Member
Join Date
Dec 1969
Posts
144

## Date & Time Difference

I have two database fields that are a date & time. Field Start Time = "2000-03-02 06:04:09:00" and Field Stop Time is "2000-04-11 10:53:51:00". How can I get the difference between the two fields in hours?<BR><BR>Thanks in advance<BR>Ray

2. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## RE: Date & Time Difference

use the datediff function and work from there

3. Supriya Guest

## RE: Date & Time Difference

Try this: convert your database fields to "date" type (using CDate function). Use the DateDiff function to get the difference. If you want the difference in hours, then ....<BR>DateDiff(h, date1, date2)<BR>This returns the interval between date1 and date2 in hours.<BR>Hope this helps.<BR>

4. Member
Join Date
Dec 1969
Posts
55

## RE: Date & Time Difference

Probably a better solution out there, but here is one way. You could first use RIGHT on each of the fields, like so:<BR><BR>startTime = Right(startDate, 11)<BR>finishTime = Right(finishDate, 11)<BR><BR>This will give us the time values only. Thus, our variables will contain:<BR><BR>startTime = "06:04:09:00"<BR>finishTime = "10:53:51:00"<BR><BR>Now, you can convert these times to total seconds by using the SPLIT function:<BR><BR>startArray = Split(startTime, ":")<BR>finishArray = Split(finishTime, ":")<BR><BR>Now we have the hours, minutes, and seconds seperated in an array element like:<BR><BR>startArray = (06, 04, 09, 00)<BR>finishArray = (10, 53, 51, 00)<BR><BR>Now, to convert everything to seconds, we use some arithmetic. First, subtract start seconds from finish seconds, like:<BR><BR>remainderSeconds = finishArray(2) - startArray(2)<BR><BR>This will give us 42. Now, subtract start minutes from finish minutes.<BR><BR>remainderMinutes = finishArray(1) - startArray(1)<BR><BR>This will give us 47.<BR><BR>Lastly, subtract hours.<BR><BR>remainderHours = finishArray(0) - startArray(0)<BR><BR>This gives us 4.<BR><BR>So, our final result is 4hrs 47 minutes and 42 seconds.<BR><BR>Of course, what if it isn&#039;t this easy? What if finish minutes is LESS than start minutes? Well, we would need to perform a check like:<BR><BR>If InStr(remainderMinutes, "-") Then<BR> &#039;number is negative. subtract one from remainderHours<BR> remainderHours = remainderHours - 1<BR>End If<BR><BR>So, if the minutes were reversed, we would have a total of 3hrs, 47 minutes and 42 seconds.

5. Member
Join Date
Dec 1969
Posts
55

## RE: Date & Time Difference

Probably a better solution out there, but here is one way. You could first use RIGHT on each of the fields, like so:<BR><BR>startTime = Right(startDate, 11)<BR>finishTime = Right(finishDate, 11)<BR><BR>This will give us the time values only. Thus, our variables will contain:<BR><BR>startTime = "06:04:09:00"<BR>finishTime = "10:53:51:00"<BR><BR>Now, you can convert these times to total seconds by using the SPLIT function:<BR><BR>startArray = Split(startTime, ":")<BR>finishArray = Split(finishTime, ":")<BR><BR>Now we have the hours, minutes, and seconds seperated in an array element like:<BR><BR>startArray = (06, 04, 09, 00)<BR>finishArray = (10, 53, 51, 00)<BR><BR>Now, to convert everything to seconds, we use some arithmetic. First, subtract start seconds from finish seconds, like:<BR><BR>remainderSeconds = finishArray(2) - startArray(2)<BR><BR>This will give us 42. Now, subtract start minutes from finish minutes.<BR><BR>remainderMinutes = finishArray(1) - startArray(1)<BR><BR>This will give us 49.<BR><BR>Lastly, subtract hours.<BR><BR>remainderHours = finishArray(0) - startArray(0)<BR><BR>This gives us 4.<BR><BR>So, our final result is 4hrs 49 minutes and 42 seconds.<BR><BR>Of course, what if it isn&#039;t this easy? What if finish minutes is LESS than start minutes? Well, we would need to perform a check like:<BR><BR>If InStr(remainderMinutes, "-") Then<BR> &#039;number is negative. subtract one from remainderHours<BR> remainderHours = remainderHours - 1<BR>End If<BR><BR>So, if the minutes were reversed, we would have a total of 3hrs, 49 minutes and 42 seconds.<BR><BR>Here is the code that will make it work:<BR><BR>&#060;%<BR>startDate = &#039;get date from DB<BR>finishDate = &#039;get date from DB<BR><BR>startTime = Right(startDate, 11)<BR>finishTime = Right(finishDate, 11)<BR><BR>startArray = Split(startTime, ":")<BR>finishArray = Split(finishTime, ":")<BR><BR>remainderSeconds = finishArray(2) - startArray(2)<BR>If InStr(remainderSeconds, "-") Then<BR> &#039;number is negative, minus one from finish minutes<BR> finishArray(1) = finishArray(1) - 1<BR> remainderSeconds = Replace(remainderSeconds, "-", "")<BR>End If<BR><BR>remainderMinutes = finishArray(1) - startArray(1)<BR>If InStr(remainderMinutes, "-") Then<BR> &#039;number is negative, minus one from finish hours<BR> finishArray(0) = finishArray(0) - 1<BR> remainderMinutes = Replace(remainderMinutes, "-", "")<BR>End If<BR><BR>remainderHours = finishArray(0) - startArray(0)<BR><BR>Total = remainderHours & ":" & remainderMinutes & ":" & remainderSeconds<BR>Response.Write Total<BR>%&#062;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•