469,898 Members | 1,574 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Trying to get Date Diff or Timespan a simple way.

6
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?

Expand|Select|Wrap|Line Numbers
  1. Dim StartTime
  2. Dim EndTime
  3.  
  4. StartTime = rs("LastUpdated") 'record in sql table stored as datetime
  5. EndTime = (Now) 'current date
  6.  
  7. response.write starttime & "<br>"
  8. response.write endtime & "<br><br>"
  9.  
  10. Dim diff1
  11. diff1 = EndTime - StartTime
  12.  
  13. Response.Write diff1 
  14.  
[Output]
3/12/2008 11:11:53 AM
3/13/2008 12:15:11 PM

1.04395833332819
[/Output]

Please help!!
Mar 13 '08 #1
5 3098
jeffstl
432 Expert 256MB
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
Mar 13 '08 #2
Pauley
6
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.

Expand|Select|Wrap|Line Numbers
  1. StartTime = rs("date") 'datetime field in the sql table
  2. EndTime = (Now) 'current date and time
  3.  
  4. response.write(DateDiff("h",EndTime,StartTime) & "<br>")
  5.  
[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!
Mar 18 '08 #3
jeffstl
432 Expert 256MB
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.

Expand|Select|Wrap|Line Numbers
  1. StartTime = rs("date") 'datetime field in the sql table
  2. EndTime = (Now) 'current date and time
  3.  
  4. response.write(DateDiff("h",EndTime,StartTime) & "<br>")
  5.  
[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
Mar 18 '08 #4
Pauley
6
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?
Mar 23 '08 #5
Pauley
6
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!
Mar 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

28 posts views Thread by Steve | last post: by
5 posts views Thread by A | last post: by
2 posts views Thread by Cyber Clone via DotNetMonster.com | last post: by
29 posts views Thread by james | last post: by
8 posts views Thread by JFB | last post: by
2 posts views Thread by Brian Shafer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.