convert seconds to hh:mm:sec

[EBee]

I have a number type field in a oracle database that is in (seconds). I am using ACCESS query to pass-through, I need to convert the time to hh:min:sec.

how can i convert in Access query or module so the number type field seconds show as hh:min:sec.

thanks
erwin

 

[MichaelRed]

Divide by 86400, format as ANY datetime format.

e.g.:

? Format(1/86400, "hh:mm:ss&quot
00:00:01


The 86400 is simply the number of SECONDS in a standard day. If your stired value is in seconds, the division converts the (long) number to the part of the day represented by that number of seconds.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[EBee]

hi,

i tried to do what you suggested but this is what it's converting to and giving me error msg

Format(1/[DUR_SIGNON],"hh:nn:ss&quot

the mm auto changes it to nn for some reason I am using Access 97 and doing this in the query criteria field.

what 'em i doing wrong here?

thanks
erwin

 

[MichaelRed]

Well, the "mm" is a typo on my part (old eyes, fumble fingers, ...) "nn" is correct for the Minutes part.

I believe the expression is entered "upside-down". Look at the expr in my example the "1" in the numerator is the "variable", and the 86400 is the CONSTant. the return value (1 Second) is 1 86400th of a day. You have the numerator / divisor reversed. IN general, MS. will do the calc anyway - but return generally "revered" (reciprical) values, so this is not the source of an "error", just a math error.

Why you get another "error" must depend on some other process. In general, oracle (and other relational dbs) will not accept VB functions, do if the expression is in a query, you may need to place it into a small function, so the QUERY just retrieves the (numeric) value and passes it to the procedure, which then returns the formatted date/time value MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[EBee]

Many thanks. . yeah. . i got it to work now

erwin

 

[EBee]

just a folow up question if you don't mind,

It works great now, I do need to calcualte this values after converting it.

I convertedit to "hh.nnss&quot so i can calculate it like number type. The problem is that I can not convert it back from "hh.nnss" to "hh:nn:ss" after doing the calculation.

For ex:

1.0530 ( 1 hour, 5 min, 30 sec)

1.0530 +
1.0005
________

2.0535 ( 2 hours, 5 min, 35 sec)

and I need to convert to 02:05:35

thanks
erwin

 

[MichaelRed]

Only way is to view the intermediate value as string and parse the charcters for use in dateserial fonction.

It is only passing weird to even display a regular time in the format you use. I would just leave the value in hte date format. IF I needed to do the weirdness of display, I would do it in the formatting of the display and not convert to oddities for storage.

Then, that why they ...

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over