Trying to get Date Diff or Timespan a simple way.

[Pauley]

Hello,

Please keep in mind when you read this post that I am a total noob when it comes to coding.

Coding in ASP
On MS Server
SQL Database/Table

I know this topic has been covered many times and many ways. In fact I've tried to use one version or another of 20 examples with no success. All I am trying to do is determine the timespan between the date a sql record was submitted and the current date.

Most of the examples I have tried to massage into my asp page have stopped the page from loading. The example I have included below does let my page load and shows my date variables and allows the subtraction to work. However, I end up with a number with a lot of decimal places after it.

I have included my code below and the output from my code. I realize it is very simple coding but I was trying to avoid having a big block of code that I don't understand. As silly as it may seem to you pros I have spent an unbelievable amount of time trying to make this work so any assistance would be greatly appreciated!!!! I have no hair left to pull out!

I'm trying to get to a point where the output displays the amount of days(if over 24 hrs) and the amount of hours.

Do I need to convert that number somehow or am I doing something completely wrong?

Dim StartTime
Dim EndTime
 
StartTime = rs("LastUpdated") 'record in sql table stored as datetime
EndTime = (Now) 'current date
 
response.write starttime & "<br>"
response.write endtime & "<br><br>"
 
Dim diff1
diff1 = EndTime - StartTime
 
Response.Write diff1 
 

[Output]
3/12/2008 11:11:53 AM
3/13/2008 12:15:11 PM

1.04395833332819
[/Output]

Please help!!

[jeffstl]

Sounds like you want to use the datediff function of VB

Explore this link to find out more. Yours should look something like this when your done, if you want to use hours as your final output, otherwise just replace the h with m for minutes, etc. Read the link to see the whole function in use.

diff1 = DateDiff(h,EndTime,StartTime)

http://www.w3schools.com/vbscript/func_datediff.asp

[Pauley]

Sounds like you want to use the datediff function of VB

Explore this link to find out more. Yours should look something like this when your done, if you want to use hours as your final output, otherwise just replace the h with m for minutes, etc. Read the link to see the whole function in use.

diff1 = DateDiff(h,EndTime,StartTime)

http://www.w3schools.com/vbscript/func_datediff.asp

Hi Jeff,

Thanks so much for the reply! I attempted to use the examples on w3schools when I began this project but had no luck. However it did help me to understand a lot. I rely on that site a lot. I was able to use a variation of the line of code you gave me and got the page to produce the hours since a record had been submitted. The line of code is below.

StartTime = rs("date") 'datetime field in the sql table
EndTime = (Now) 'current date and time
 
response.write(DateDiff("h",EndTime,StartTime) & "<br>")
 

[Output]
-21
[/Output]

However I still had some issues to deal with.

1. The value that was returned was a negative number. Im not sure why since the EndTime is always higher than the StartTime. I reversed the EndTime and StartTime and it returns a positive number.

I'm not sure it thats the correct way to resolve that or not?

2. I have a datetime format field in the sql table called date where I submit EndTime. Which is just EndTime = (Now) For some reason when I submit a record the date always gets submitted as the current date but the time always gets entered as 12:00:00 AM.

Any idea why it won't record the time from EndTime = (Now)?

Any assistance you can offer would be greatly appreciated!

[jeffstl]

Hi Jeff,

Thanks so much for the reply! I attempted to use the examples on w3schools when I began this project but had no luck. However it did help me to understand a lot. I rely on that site a lot. I was able to use a variation of the line of code you gave me and got the page to produce the hours since a record had been submitted. The line of code is below.

StartTime = rs("date") 'datetime field in the sql table
EndTime = (Now) 'current date and time
 
response.write(DateDiff("h",EndTime,StartTime) & "<br>")
 

[Output]
-21
[/Output]

However I still had some issues to deal with.

1. The value that was returned was a negative number. Im not sure why since the EndTime is always higher than the StartTime. I reversed the EndTime and StartTime and it returns a positive number.

I'm not sure it thats the correct way to resolve that or not?

2. I have a datetime format field in the sql table called date where I submit EndTime. Which is just EndTime = (Now) For some reason when I submit a record the date always gets submitted as the current date but the time always gets entered as 12:00:00 AM.

Any idea why it won't record the time from EndTime = (Now)?

Any assistance you can offer would be greatly appreciated!

Q1) StartTime should be first, End Time second to get the difference you want.

Q2) To store the returned value of the Now() function in a way you want you will need to use the VB Format function

Format(Now, "MM/DD/YYYY") ---will return 03/18/2008 - However I think for ASP its called FormatDateTime() but works the same.

http://www.w3schools.com/vbscript/fu...atdatetime.asp

[Pauley]

Q1) StartTime should be first, End Time second to get the difference you want.

Q2) To store the returned value of the Now() function in a way you want you will need to use the VB Format function

Format(Now, "MM/DD/YYYY") ---will return 03/18/2008 - However I think for ASP its called FormatDateTime() but works the same.

http://www.w3schools.com/vbscript/fu...atdatetime.asp

Hi Jeff,

Still working on this thing and I've got it down to one problem. I have my date formatted as EndTime = FormatDateTime(Now(),0)

When I write out the EndTime variable to the screen before submitting it writes it as it should with both the current date and time. However, once I submit it to my datetime field in my table it still records the time portion of the date as 12:00:00 AM instead of the current time.

Displays on screen before submitting as: 3/23/2008 2:10:24 PM
Records in sql table datetime field as: 3/23/2008 12:00:00 AM

Any idea why it won't record the proper time?

[Pauley]

Hi Jeff,

Still working on this thing and I've got it down to one problem. I have my date formatted as EndTime = FormatDateTime(Now(),0)

When I write out the EndTime variable to the screen before submitting it writes it as it should with both the current date and time. However, once I submit it to my datetime field in my table it still records the time portion of the date as 12:00:00 AM instead of the current time.

Displays on screen before submitting as: 3/23/2008 2:10:24 PM
Records in sql table datetime field as: 3/23/2008 12:00:00 AM

Any idea why it won't record the proper time?

Nevermind, I got it!

Thanks for all the help!