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

RE: Date & Time Difference
use the datediff function and work from there

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>

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'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> '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.

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'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> '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><%<BR>startDate = 'get date from DB<BR>finishDate = '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> '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> '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>%>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

