July 22, 2008 at 9:09 am
I'm trying to display an integer field as HH:MM:SS but I can only get the MM:SS to show. The HH part is being displayed as zero and I know there are hours. I'll explain below.
The value for the Integer field (StaffTime) is 692238, which would translate to
192h:17m:18s. However I can only get my syntax to display it as
00h:17m:18s. Below is my syntax.
HoursLogged = convert(varchar(14), dateadd(second, sum(c.ti_stafftime),0), 108)
Can anybody shed a light as to what may be wrong with my code?
thx,
john
July 22, 2008 at 9:28 am
I think this is because it overflows beyond 24 and the time spec doesn't show hours > 23.
If you want this converted, I think you need to do the math to get hours, minutes, seconds ( %60, %3600, etc) and then combine them, converting back to strings.
July 22, 2008 at 9:34 am
And how would I do that? Meaning what modifications do I need to make to my code?
thx,
John
July 22, 2008 at 11:24 am
Something like this?
DECLARE @Sample INT
SET @Sample = 692238
SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 86400, '00:00:00'), 108), 1, 2, CAST(@Sample / 3600 AS VARCHAR(12)))
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply