Convert Seconds from Midnight to Time 1

[jpl458]

Just established an ODBC connection to Non-Microsoft database that stores time of day as seconds past midnight. Was wondering if someone has a slick way of converting or has a function that does the trick.

Thanks

jpl

 

[MajP]

Public Function getTimeFromSeconds(lngSeconds As Long) As Date
Const secondsInDay = 86400
getTimeFromSeconds = lngSeconds / secondsInDay
End Function

 

[jpl458]

Thanks for the fast response - Will use it first thing Monday.

Thanks again

 

[MajP]

vb saves time as an integer part representing the date and a decimal part representing the time. For example the date and time right now is
40278.8544907407

where 40278 represents how many full days have elapsed since the vb base date (12/30/1899). .8544907407 represents the fraction of the next day which is 8:30:28 pm. So time is represented in vb as the fraction of a day. If you divide your seconds by the seconds in a day you get the fraction of a day, and thus it becomes the same as the vb time value.

Every time you see is some formatted version of this fraction.

 

[Golom]

If you want to use a builtin function

TimeSerial ( 0, 0, lngSeconds )

 

[jpl458]

Golom, tried using the timeserial approach and most of the time came up as #Error. For Exmple, here is the expression used:

HourofDay: TimeSerial(0,0,[RecTracHist]![PSH_Time])

Here is sample of result (PSH_Time=minutes past midnight and the HourofDay column contains the timeserial function above. The #errors did not copy into this msg, but where a blank is in the listing - that's an error. I sorted the result ascending and found that the timeserial function stopped working after 9 hrs and 5 minute.

I feel I am confused - to say the least.

PSH_Time HourofDay
32723 9:05:23 AM
32723 9:05:23 AM
32725 9:05:25 AM
32729 9:05:29 AM
32753 9:05:53 AM
32754 9:05:54 AM
32772
32773
32773
32775
32781
32796
32798

Thanks again

jpl

 

[MajP]

This is why I recommended a user defined function

When any argument exceeds the normal range for that argument, it increments to the next larger unit as appropriate. For example, if you specify 75 minutes, it is evaluated as one hour and 15 minutes. If any single argument is outside the range -32,768 to 32,767, an error occurs. If the time specified by the three arguments causes the date to fall outside the acceptable range of dates, an error occurs.

 

[jpl458]

Just ran Golom's function and it worked like a champ, couldn't wait till Monday.

Thanks to both you folks, really appreciate it.

jpl458

 

[Golom]

MajP is correct. The arguments to TimeSerial are integer values and are limited to 32767. They produce an overflow if the value is greater than that. You can however use DateAdd

? DateAdd ("s",32798,0)
9:06:38 AM

 

[jpl458]

Thanks again. the old 2 to the 15th -1 trick.

Thanks for the lesson.

jpl

 

[SkipVought]

...or using pure math to convert seconds to days...

? CDate(32798/60/60/24)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]