Format Time [Resolved]

[RobDog888]

I need to format a serial time duration into "HH:MM"SS".
The time is from a stored procedure that returns the duration
that the user is logged in, but I'm not sure on the measure of
time SQL uses. It looks like miliseconds. I guess I need something
like the reverse of TimeSerial function.

Ex.
77984 = ??? hours, minutes, or seconds?

Is there an easy way to do this formatting?

Thanks.

[vbasicgirl]

i usually use this.

Debug.Print CDate(77984 / 86400) 'if seconds
 
Debug.Print CDate((77984 / 1000) / 86400) 'if milliseconds

casey.

[RobDog888]

Thanks, but it turns out that the time filed in SQL is just keeping
track of how much time the processor has spent processing
queries for a particular app. I was unsure because there was no
indication that it was a running time field or not. So I found
anohter filed that stamps the login date/time. So I will have to do
a datadiff on that for each entry.

Thanks anyway.

[szlamany]

RD...

We have an APPCONNECT_T table that we record the "connection" to the DB that a user makes.

We have a SPROC called APPCONNECT_INSERT that creates a row with the SYSTEM_USER, the GETDATE() - we also pass the "time" of the local workstation to the SPROC - so we know the PC time and SERVER time. We pass the APP.NAME, and APP.PATH and APP.VERSION.

We even pass the RESOLUTION of the workstation.

Inside the SPROC we find out the HOSTNAME and also put that into the record.

We return the IDENTITY column of the row that got added and when the application exits, we "flag" the record as CLOSED with the date/time (server and workstation) of when they left and the "number of rows" they changed.

It's a really neat little statistical file.

At one of my customer sites, they are over 80,000 connections already.

[RobDog888]

Sounds like it was allot of work to set up, but its nice to have once its up.

Another related question, say I have 100 seconds and I need
to display it as "hh:mm:ss". How could I do that? The format
function is not displaying it correct because if I have 100 seconds
it displays 12:01:00 AM. I want it do be 00:01:40 seconds.

Thanks.

Ps. going home now, but will be back online in an hour and a half.

[szlamany]

One of our 10 commandments (actually 2 of them) are to format dates in the SPROCS for return to VB.

We only take dates from SPROCS as STRINGS.

We use either:

,Convert(varchar(10),ST.DOB,101) "DOB"

Giving us mm/dd/yyyy (always padding the MM with a leading 0 - such as 09/01/2004)

or we use:

,Convert(char(23),ST.TDate,121)	"TDate"

Which gives you a MM-DDD-YYYY HH.MM.SS.MMM string.

I would investigate formatting that time in the SPROC using CONVERT - it has so many nice formatting options...

[szlamany]

I'm really starting to like SQL

Try this (in QUERY ANALYZER):

declare @seconds int

set @seconds=100

print right(convert(char(23),dateadd(ss,@seconds,'01-01-1900'),121),12)

You get "00:01:40.000"

The 01-01-1900 is just a bogus date to add the seconds to - the RIGHT cuts it off. 108 instead of 121 does just hh:mm:ss - give that try as well - might be less code...

Quick note - the milliseconds are not real - I think they skip every 6 numbers...

[RobDog888]

szlamany, the problem is that i am getting the time from the sp I
posted here in the databases forum. It will return a recordset
so either I calculate a duration in the sp or calculate it in vb once
for each record.

[Merri]

Can't store the seconds in a date, because that'd make you use locale when you want to display it. But you can always do this:

Public Function FormatHHMMSS(ByVal Hours As Byte, ByVal Minutes As Byte, ByVal Seconds As Byte) As String
    Minutes = Minutes + (Seconds \ 60)
    Hours = Hours + (Minutes \ 60)
    Minutes = Minutes Mod 60
    Seconds = Seconds Mod 60
    FormatHHMMSS = Format$(Hours, "00") & ":" & Format$(Minutes, "00") & ":" & Format$(Seconds, "00")
End Function

[VbMafia]

just try this

Label1.Caption=format(time,"hh:mm:ss"

Thanks hopefully help.....

[Maverickz]

I THINK this may do what you need:

=================================
Public Function Secs2Time(ByVal myTimeInSecs As Integer) As String
Dim myHours As Integer
Dim myMin As Integer
Dim mySec As Integer
Dim myTime As String
myTimeInSecs = Text1.Text
myHours = myTimeInSecs \ 3600
myTimeInSecs = myMod(myTimeInSecs, 3600)
myMin = myTimeInSecs \ 60
mysecs = myMod(myTimeInSecs, 60)
Secs2Time = FormatDateTime(myHours & ":" & myMin & ":" & mySec, vbShortTime)
End Function

Private Function myMod(str1, str2)
Result = Int(str1 \ str2)
Result = Result * str2
myMod = str1 - Result
End Function

=================================

Now if you put in 1000 seconds you will get 00:16 which is correct to the minute. If you remove the FormatDateTime part from the last line of the Secs2Time function so it looks like this:

Secs2Time = myHours & ":" & myMin & ":" & mySec

Then the output is 0:16:4 (which is correct but you will have to manualy pad the numbers to make it 00:16:40 or use some other formating routine. Either way this should get you close enough to what you needed you can tweak it how ever you need.

NOTE: I know VB has a built in Mod function but I have had it break when using large numbers, but the little myMod function has never failed yet.

The Mav

[Merri]

I still have to remind about locale: each time you use Format$ to display time, Format$ uses the current locale of the system. Where a Finnish locale would display 0:00:00 (24 hour system), English locale would show 12:00:00 AM (12 hour system) - thus you can't use it to format time always in a certain way.


And more about my function: if you want to give bigger values to it, just change Bytes to Longs.

[Maverickz]

Actually Merri I set the format to "vbShortTime" which is 24 hr time format that is universaly hh:mm with no seconds or am/pm indicator so it's NOT localized.

Alternativly, you could just manualy pad the numbers instead of using any formating either.

[Merri]

I weren't talking about FormatDateTime, I were talking about Format - a big difference there

Though, as vbShortTime doesn't show seconds, then there is no point giving them like you did - and you'd end up doing something that is similar to my code if you wanted to see the seconds anyway

[szlamany]

Originally posted by RobDog888
szlamany, the problem is that i am getting the time from the sp I
posted here in the databases forum. It will return a recordset
so either I calculate a duration in the sp or calculate it in vb once
for each record.

RD - like I said - we attempt to work with dates and times as strings in VB - due to all the odd locality issues, etc.

CONVERT() in T-SQL is a "guaranteed" format - the value you choose will yield the results you want always.

If passing it out of the SPROCS as an "already formatted VARCHAR()" value is not possible, then I would use most of what MAVERICKZ posted - do you own division and whatnot to get it down to LONGS with minutes and seconds and then format them. I personnaly would use RIGHT('00' & CStr(minutes),2) to get it formatted as "MM".

Plus - just a point on BLACK-BOX functionality - the SPROC should return the data in it's final format. Thats a more proper ACID programming approach...

[RobDog888]

szlamany, I am going to try to add a computed field in the sp.
This will allow me to have a login_time field and a duration field. I
may try it today if I get some time to connect to the office
otherwise I will test it out on Monday.

Thanks to all.

[dw85745]

Merri:

Where did you get this idea, first I heard of it

I still have to remind about locale: each time you use Format$ to display time, Format$ uses the current locale of the system.

David

[RobDog888]

dw85745, from the MSDN Help file on Format$:

For the date separator (/), time separator (:), and AM/ PM literal,
the actual formatted output displayed by your system depends
on the locale settings on which the code is running. When times
and dates are displayed in the development environment, the
short time format and short date format of the code locale are
used. When displayed by running code, the short time format and
short date format of the system locale are used, which may differ
from the code locale.

[dw85745]

Thanks RobDog888:

I misread Merri posting as was thinking he was referring to the local time somehow changing the actual time NOT just how it is being displayed. My Error.

David

[RobDog888]

I made the changes in the sp for best performance.

Thanks to all and check out the new "Resolved" green check mark
I help implement!