Seconds to dd:hh:mm:ss
-
Hi, another basic question as you are really good and quick 🙂
I have a field, a time field, containing the number of seconds.
I would like to convert it, easly to a number of day, hours, minutes and seconds ...
Cheers,
-
look at the datepart function.
datepart(hh,mydate)
will give you an integer of the hour.
-
Well, doesn't seem to work fine.
I've got an 'Arithmetic overflow error converting expression to data type datetime'
I have a value for exemple: 508621 (seconds)
I want to display: 5 days 21 hour 17 mn 1 sec
-
DECLARE @date datetime
SET @date = DATEADD(second,508621,0)
SELECT
CAST(DATEPART(dayofyear,@date)-1 as varchar) + ' days ' +
CAST(DATEPART(hour,@date) as varchar) + ' hour ' +
CAST(DATEPART(minute,@date) as varchar) + ' mn ' +
CAST(DATEPART(second,@date) as varchar) + ' sec'
Far away is close at hand in the images of elsewhere.
Anon. -
Ok, one more question because I m getting an error ...
My field, TS_TIME_ASSIGNED is an integer
If I replace the '508621' by my field TS_TIME_ASSIGNED in the second line (SET @date = DATEADD(second,TS_TIME_ASSIGNED,0)), I have the following error:
Server: Msg207, Level 61, State 3, Line 2
Invalid column name 'TS_TIME_ASSIGNED
But the name is correct ...
-
OK try this, may work better as no conversion to datetime required
SELECT
CAST(TS_TIME_ASSIGNED / 86400 as varchar) + ' days ' +
CAST((TS_TIME_ASSIGNED % 86400) / 3600 as varchar) + ' hour ' +
CAST(((TS_TIME_ASSIGNED % 86400) % 3600) / 60 as varchar) + ' mn ' +
CAST(((TS_TIME_ASSIGNED % 86400) % 3600) % 60 as varchar) + ' sec'
FROM
Far away is close at hand in the images of elsewhere.
Anon. -
You might try using a function like this:
CREATE FUNCTION fn_GetDays(@InSecs As int)
RETURNS varchar(36) AS
BEGIN
declare
@GetDays varChar(200)
-- Get the Days, Hours, Minutes, Seconds returned with input in Seconds
-- Use: Select traffic.dbo.fn_Getdays(96453) as Days
declare Cursor1 cursor for
Select
CAST(@InSecs / 86400 as varchar) + ' days ' +
CAST((@InSecs % 86400) / 3600 as varchar) + ' hours ' +
CAST(((@InSecs % 86400) % 3600) / 60 as varchar) + ' mins ' +
CAST(((@InSecs % 86400) % 3600) % 60 as varchar) + ' secs'
open cursor1
fetch next from cursor1 into @getdays
close cursor1
deallocate cursor1
return @GetDays
END
-
Don't need to use a cursor
CREATE FUNCTION fn_GetDays(@Secs As int)
RETURNS varchar(36) AS
BEGIN
DECLARE @GetDays varchar(36)
-- Get the Days, Hours, Minutes, Seconds returned with input in Seconds
-- Use: Select .dbo.fn_GetDays(96453) as Days
SET @GetDays =
CAST(@Secs / 86400 as varchar) + ' days ' +
CAST((@Secs % 86400) / 3600 as varchar) + ' hours ' +
CAST(((@Secs % 86400) % 3600) / 60 as varchar) + ' mins ' +
CAST(((@Secs % 86400) % 3600) % 60 as varchar) + ' secs'
RETURN @GetDays
END
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you David. Trying to stay away from cursors. Old habits die hard!!
-
You certainly don't need a cursor and, if you don't want, you don't need a function... "Trust the force, Luke."
SELECT CONVERT(VARCHAR(10),SecondsCol/86400)+':'
+ CONVERT(VARCHAR(8),DATEADD(ss,SecondsCol,0),108)
FROM yourtable
The forces to trust are:
1. Integer math
2. Instrinsic datetime formats
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Pretty nifty Jeff. Thanks for the coaching. I use functions a lot because I hate to remember and then type all the syntax!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply