Datetime Format
-
Hi folks,
I wonder how to do the following thing:
I got the time difference in second by using: DateDiff, e.g.
select DateDiff(s, '2005-12-24 12:00:00', '2005-12-24 13:00:01')
returns: 3601 (seconds)
But I want hh:mm:ss format for the result, how to do that?
Thanks.
-
This might give you something to work with:
DECLARE @mytime INT
DECLARE @myhour INT
DECLARE @mymin INT
DECLARE @mysec INT
SET @mytime = 3601
SET @myhour = (SELECT @mytime / 1440)
SET @mytime = (SELECT @mytime % 1440)
SET @mymin = (SELECT @mytime/60)
SET @mysec = (SELECT @mytime%60)
SELECT CAST(@myhour AS VARCHAR(2)) + ':' + CAST(@mymin AS VARCHAR(2)) + ':' + CAST(@mysec AS VARCHAR(2))
There might be a better way, but that's what I came up with.
-SQLBill
-
well, if the dates are less that one day appart you can:
declare @d1 as datetime, @d2 as datetime
select @d1 = '2005-12-24 12:00:00', @d2 = '2005-12-24 13:00:01'
select convert( varchar(8), @d1 - @d2, 8)
Cheers,
* Noel -
Hi noeld, your sql comes up 22:59:59 which is obviously incorrect.
Slightly change here:
select convert( varchar(8), @d2 - @d1, 8)
SQLBill, your sql also comes out an incorrect answer:2:12:1
This is what I use:
PRINT CONVERT(VARCHAR(8), DATEADD(SS, 3601, '00:00:00'), 108)
The answer is:
01:00:01
Thank you guys anyway.
-
You are right about my results being wrong. Change the 1440 to 3600 in my scripts and it should return the correct answer. But it looks like you found a better solution.
-SQLBill
-
Well as you already spotted I just swapped the difference but you got the Idea.
By the way the solution you say you use has nothing to do with your original requeriments of having two dates as the input
Cheers,
* Noel -
Hi
You might try
SELECT CONVERT(VARCHAR,DATEADD(s,DateDiff(s, '2005-12-24 12:00:00', '2005-12-24 13:00:01'),'1 Jan, 1900'),108)
This should work fine as long as the difference between the two dates is never more than 23:59:59
Regards
Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply