Convert Seconds for a given time

• sqlusers

  SSCrazy

  Points: 2463

  More actions

  November 4, 2011 at 2:40 am

  #247284

  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.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 4, 2011 at 2:51 am

  #1403108

  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

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 4, 2011 at 3:04 am

  #1403117

  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

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• matzer

  SSCrazy Eights

  Points: 9727

  More actions

  November 4, 2011 at 3:31 am

  #1403121

  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

• John Esraelo-498130

  SSCertifiable

  Points: 5916

  More actions

  November 6, 2011 at 11:21 am

  #1403726

  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