Convert Number to HH:MM:SS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a table that is formatted as a number which is actually
seconds. Is there a way to convert this number into an hh:mm:ss time format?
 
I have a field in a table that is formatted as a number which is actually
seconds. Is there a way to convert this number into an hh:mm:ss time format?
Click to expand...

well, you want nn (miNutes) not mm (Months) but...

[field] \ 3600 & Format(([field] \ 60) MOD 60, ":00") & Format([field]
MOD 60, ":00")

should do it for you.

John W. Vinson[MVP]
 
It works to a point. I get the format I want, but all of the fields are
0:00:00, there are no numbers and there should be. What next?
Thanks for your quick reply.
L

John Vinson said:
I have a field in a table that is formatted as a number which is actually
seconds. Is there a way to convert this number into an hh:mm:ss time format?
Click to expand...

well, you want nn (miNutes) not mm (Months) but...

[field] \ 3600 & Format(([field] \ 60) MOD 60, ":00") & Format([field]
MOD 60, ":00")

should do it for you.

John W. Vinson[MVP]
Click to expand...
 
It works to a point. I get the format I want, but all of the fields are
0:00:00, there are no numbers and there should be. What next?
Thanks for your quick reply.
Click to expand...

What is the actual content of the field in your table? Could you post
the SQL of the query that you're using, along with the actual contents
of this seconds field and the resulting expression?

John W. Vinson[MVP]
 
SQL:
SELECT [Staffed Time]/3600 & Format(([Staffed Time]/60) Mod 60,":""00""") &
Format([Staffed Time] Mod 60,":""00""") AS [Time]
FROM [tbl Aux Time];
Result:
7.51555555555556:00:00
Thanks for the help

lmr said:
It works to a point. I get the format I want, but all of the fields are
0:00:00, there are no numbers and there should be. What next?
Thanks for your quick reply.
L

John Vinson said:
I have a field in a table that is formatted as a number which is actually
seconds. Is there a way to convert this number into an hh:mm:ss time format?
Click to expand...

well, you want nn (miNutes) not mm (Months) but...

[field] \ 3600 & Format(([field] \ 60) MOD 60, ":00") & Format([field]
MOD 60, ":00")

should do it for you.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
SQL:
SELECT [Staffed Time]/3600 & Format(([Staffed Time]/60) Mod 60,":""00""") &
Format([Staffed Time] Mod 60,":""00""") AS [Time]
FROM [tbl Aux Time];
Result:
7.51555555555556:00:00
Thanks for the help
Click to expand...

Use the \ - backslash integer divide operator - instead of /; and get
rid of the extra quotes:

SELECT [Staffed Time]\3600 & Format(([Staffed Time]\60) Mod 60,"\:00")
& Format([Staffed Time] Mod 60,"\:00") AS [Time]>FROM [tbl Aux Time];

The ""00"" format will insert the literal characters 00 in the field
regardless of the time value; the \:00 makes the : a forced literal.
Sorry about the limited testing!

John W. Vinson[MVP]
 
Thank you very much for your help. It works great!

John Vinson said:
SQL:
SELECT [Staffed Time]/3600 & Format(([Staffed Time]/60) Mod 60,":""00""") &
Format([Staffed Time] Mod 60,":""00""") AS [Time]
FROM [tbl Aux Time];
Result:
7.51555555555556:00:00
Thanks for the help
Click to expand...

Use the \ - backslash integer divide operator - instead of /; and get
rid of the extra quotes:

SELECT [Staffed Time]\3600 & Format(([Staffed Time]\60) Mod 60,"\:00")
& Format([Staffed Time] Mod 60,"\:00") AS [Time]>FROM [tbl Aux Time];

The ""00"" format will insert the literal characters 00 in the field
regardless of the time value; the \:00 makes the : a forced literal.
Sorry about the limited testing!

John W. Vinson[MVP]
Click to expand...
 
Back
Top