• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

How to convert Seconds in to Hour Minute Second format

Hi,
In Asp we are able to find the time diffrence between two time/date values with the help of Datediff function. But this will return the value either in hour or in minute or in second. I want to know how to convert or display the data in Hour:Minute:Second (00:00:00) format if the Datediff function returns the value in seconds.
rsUsers1.open "SELECT  sum(datediff(s,(LoginTime),(LogoutTime))) AS Prod FROM project_login where project_login.Name='" &mname1&"' and project_login.InputDate>='"&fdate&"' And project_login.InputDate<='"&tdate&"'",Conn
 
 
I am extracting the Sum of time diffrence between two times of a particular date in seconds and assigning the value to a variable as 
abc=rsUsers1("Prod")
in the next line we when use the formulae 
 
abc1=DateAdd("s",abc,cdate("1/1/0000"))
 
I am getting the error message  
Error Type:
Microsoft VBScript runtime (0x800A005E)
Invalid use of Null
this error message I am geeting on the same line number on which we have the line of DateADD function.

Open in new window

0
rohit_jha7
Asked:
rohit_jha7
  • 2
  • 2
1 Solution
 
David H.H.LeeCommented:
Hi rohit_jha7,
Try this:
<%
Call ElapsedTime(cdate('6/1/1999 8:23:00PM')-cdate('6/1/1999 8:12:12AM'))

Sub ElapsedTime (Interval)
  Dim x
  x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
  Response.write(x) & "<br>"
  x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
     & " Minutes:Seconds"
  Response.write(x) & "<br>"
  x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
     & " Hours:Minutes:Seconds"
  Response.write(x) & "<br>"
  x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
     & " Hours " & Format(Interval, "nn") & " Minutes " & _
     Format(Interval, "ss") & " Seconds"
  Response.write(x) & "<br>"
End Function
%>

Notice that the following values appear:
-----------------------------------------------------------
43848 Seconds
730:48 Minutes:Seconds
12:10:48 Hours:Minutes:Seconds
0 days 12 Hours 10 Minutes 48 Seconds

http://support.microsoft.com/kb/210276

0
 
SharathData EngineerCommented:
can you check this.
rsUsers1.open "SELECT  sum(datediff(s,(LoginTime),(LogoutTime))) AS Prod FROM project_login where project_login.Name='" &mname1&"' and project_login.InputDate>='"&fdate&"' And project_login.InputDate<='"&tdate&"'",Conn
abc=rsUsers1("Prod")
rsUsers2.open"select case when Prod/3600 < 10 then '0' + convert(varchar(10),Prod/3600) else convert(varchar(10),Prod/3600) end,
       case when (Prod%3600)/60 < 10 then '0' + convert(varchar(10),(Prod%3600)/60) else convert(varchar(10),(Prod%3600)/60) end,
       case when Prod%60 < 10 then '0' + convert(varchar(10),Prod%60) else convert(varchar(10),Prod%60) end as Prod1"
 
abc1=usUsers1("Prod1")
 

Open in new window

0
 
rohit_jha7Author Commented:
Both the suggestions are not working in the case of ASP where data is fetched from SQL
0
 
SharathData EngineerCommented:
my solution will work in sql server and you can adapt that in ASP code.
0
 
rohit_jha7Author Commented:
If I use this code after my SQL code

rsUsers2.open"select case when Prod/3600 < 10 then '0' + convert(varchar(10),Prod/3600) else convert(varchar(10),Prod/3600) end,
       case when (Prod%3600)/60 < 10 then '0' + convert(varchar(10),(Prod%3600)/60) else convert(varchar(10),(Prod%3600)/60) end,
       case when Prod%60 < 10 then '0' + convert(varchar(10),Prod%60) else convert(varchar(10),Prod%60) end as Prod1"
 
abc1=rusUsers1("Prod1")
response.write abc1
I am getting the error message Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/nestle/project/agentproduction1.asp, line 428, column 129
the line number is same on which I have written the new select statement
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now