November 4, 2011 at 2:40 am
Hi Folks,
I have an requirement that, i need to convert all the times into seconds. Based on the below query i have achived that.
DECLARE @Var VARCHAR(20)
SET @Var = '10:10:10'
SELECT (SUBSTRING(@Var,0,CHARINDEX(':',@Var)) * 3600)
+ (SUBSTRING(@Var,CHARINDEX(':',@Var) + 1,2) * 60)
+ (SUBSTRING(@Var,CHARINDEX(':',@Var,CHARINDEX(':',@Var) + 1) + 1,10))
But sometime i am getting only minutes and seconds/only seconds without hours/minutes. Ex, SET @Var = ':10:10' / SET @Var = ':10'
I need a query that will work even if without Hours/Minutes. How can i achieve that. Please guide me on this.
November 4, 2011 at 2:51 am
I think you'd be best investigating converting your variable to datetime and then using the DATEPART function. But, if you insist on doing it as above, something like this may work:
SET @var = RIGHT('00:00:00' + @var, 8)
That assumes that your variable starts with ":" if it isn't full length. If it doesn't, you'll need to make the code a bit more sophisticated.
John
November 4, 2011 at 3:04 am
John Mitchell-245523 (11/4/2011)
I think you'd be best investigating converting your variable to datetime and then using the DATEPART function. But, if you insist on doing it as above, something like this may work:
SET @var = RIGHT('00:00:00' + @var, 8)That assumes that your variable starts with ":" if it isn't full length. If it doesn't, you'll need to make the code a bit more sophisticated.
John
LOL!! I didn't think of that, so worked out a bit more complicated a solution 🙂
DECLARE @TABLE AS TABLE ([VAR] VARCHAR(20))
INSERT INTO @TABLE
SELECT '10:11:12'
UNION ALL SELECT ':10:11'
UNION ALL SELECT ':10'
SELECT (ISNULL(hrs,0) * 3600) + (ISNULL(mins,0) * 60) + ISNULL(sec,0)
FROM (SELECT SUBSTRING([VAR],NULLIF(PATINDEX(start, [VAR]),0),2) AS hrs,
SUBSTRING([VAR],NULLIF(PATINDEX(mid, [VAR]),0)+1,2) AS mins,
SUBSTRING([VAR],NULLIF(PATINDEX(fin, [VAR]),0)+1,2) AS sec
FROM (SELECT '[0-9][0-9]:%' AS start, '%:[0-9][0-9]:%' AS mid,
'%:[0-9][0-9]' AS fin) a
CROSS JOIN @TABLE) a
Then converted it back from using a table variable to just a variable.
DECLARE @Var VARCHAR(20)
SET @Var = '10:10:10'
SELECT (ISNULL(hrs,0) * 3600) + (ISNULL(mins,0) * 60) + ISNULL(sec,0)
FROM (SELECT SUBSTRING(@Var,NULLIF(PATINDEX(start, @Var),0),2) AS hrs,
SUBSTRING(@Var,NULLIF(PATINDEX(mid, @Var),0)+1,2) AS mins,
SUBSTRING(@Var,NULLIF(PATINDEX(fin, @Var),0)+1,2) AS sec
FROM (SELECT '[0-9][0-9]:%' AS start, '%:[0-9][0-9]:%' AS mid,
'%:[0-9][0-9]' AS fin) a ) a
November 4, 2011 at 3:31 am
As an alternative
SELECT
DATEDIFF(S, 0, CAST('1900-01-01T' + LEFT('00:00:00', 8 - LEN(AnyTime)) + AnyTime AS DATETIME))
FROM
(
SELECT '1' UNION ALL -- 1 second
SELECT '15' UNION ALL -- 15 seconds
SELECT ':15' UNION ALL -- 15 seconds
SELECT '1:15' UNION ALL -- 75 seconds
SELECT '10:15' UNION ALL -- 615 seconds
SELECT ':10:15' UNION ALL -- 615 seconds
SELECT '1:10:15' UNION ALL -- 4215 seconds
SELECT '10:10:15' -- 36615 seconds
) Data(AnyTime)
Edit: Changed 2008 variant to 2005
November 6, 2011 at 11:21 am
I am not sure about the exact scenario and the situation but here is something that might help you in determining your solution:
declare @FirstTime datetime
declare @SecondTime datetime
set @FirstTime = '2011-11-05 10:00 PM'
set @SecondTime = '2011-11-06 10:00 am'
select @FirstTime, @SecondTime
select DATEDIFF(SECOND, @FirstTime, @SecondTime )
select DATEDIFF(MINUTE , @FirstTime, @SecondTime )
select DATEDIFF(HOUR, @FirstTime, @SecondTime )
----------------------- -----------------------
2011-11-05 22:00:00.000 2011-11-06 10:00:00.000
-----------
43200
-----------
720
-----------
12
Cheers,
John Esraelo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply