1

I'm requiring assistance in converting seconds to a more readable format and hence needing help converting it.

For example, if my columns are giving the following output (in seconds):

                         156
                         253
                          20
                          85
                          95
                         252

I need to turn this into something like this:

                        02:36
                        04:13
                        00:20
                        01:25
                        01:35
                        04:12

My Query producing this output:

datediff([second], (Select TOP 1 [ActualStart] 
                   from Runtime_CTE 
                   where HelperObjectID = [Report ID] ORDER BY ActualStart DESC), 
          [Previous Run]) as [Run Time]

Could someone help me out in updating my query?

Thanks.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Jona
  • 327
  • 4
  • 19

1 Answers1

6

Add the value to the time midnight:

SELECT DATEADD(SECOND, YourColumn, CONVERT(time(0), '00:00')) AS TimeValue
FROM YourTable...

For example, the value 156 returns 00:02:36:

SELECT DATEADD(SECOND, 156,CONVERT(time(0), '00:00'));
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Hi Larnu, is there a way to eliminate miliseconds here? – Eray Balkanli Jun 27 '19 at 13:36
  • Worth mentioning that it will only work for values less than 86400 (number of seconds in a day) – Zohar Peled Jun 27 '19 at 13:36
  • @Larnu, when I run your query in SSMS2017, my result is: 00:02:36.0000000, I wonder if I can get rid of .0000000 without using varchar? – Eray Balkanli Jun 27 '19 at 13:37
  • @ErayBalkanli are you using `time(7)` instead of `time(0)`? I can't replicate the behaviour in SQL Server 2012 - 2017. – Thom A Jun 27 '19 at 13:39
  • @Larnu, ah OK, I was using like convert(time,'00:00'), you added (0) later i believe, thank you! upvoted – Eray Balkanli Jun 27 '19 at 13:40
  • I had `time(0)` in the top answer the whole time; I had omitted the `(0)` in the latter example in error. – Thom A Jun 27 '19 at 13:40
  • @Larnu You can use just `SELECT DATEADD(s, 156, 0)` – Panagiotis Kanavos Jun 27 '19 at 13:41
  • @Larnu Hi Larnu, thanks for your answer but however, I still need to use the datediff because I'd like to know the difference from the starting and ending time duration. How would I also add that in here? thanks. – Jona Jun 27 '19 at 13:46
  • What starting or ending time @LifeOfJona? YOu only have one column. – Thom A Jun 27 '19 at 14:03
  • @Larnu The query that I posted produces just one of my columns and I need the datediff in there in order to produce it. How can I implement your query in my then? – Jona Jun 27 '19 at 14:14
  • Without the full query, I can't answer your question, @LifeOfJona . I can only answer with the information I have and the question you ask. There's no `CASE` expression in your query, so i can't tell you how it should appear in the `THEN`, but you should easily be able to implement the above into any expression. if you don't understand what the above does, you should be asking about that, so you *can* implement it. – Thom A Jun 27 '19 at 14:17
  • As you've only asked how to convert a value like `156` into a time, that's what this answer does. Perhaps you need to ask a new question, with your next requirement. Like i said, there's nothing in what you ask on how to implement the logic into a `DATEDIFF` or use in a `CASE` expression. – Thom A Jun 27 '19 at 14:19