SQL query seconds to date (Ssms12)

Question

on the stackoverflow site in fount Thad select CONVERT(CHAR(10), DATEADD(second, MAX(60), 0), 108) will result in 00:01:00 (that's true btw :-) )

now I want to integrate this in the following query (for Ssms12):

SELECT Run.TaskName,history.runtime
,CONVERT(CHAR(10), DATEADD(second, MAX(history.runtime), 0), 108)

FROM dbo.history
INNER JOIN Run
ON dbo.history.TaskID=Run.TaskID

whatever I try, I can't get this to work in the above example, does anyone have an idea how to fix this?

error: Column 'Run.TaskName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Shushil Bohara · Accepted Answer · 2016-09-19T11:31:10.503

1

You have to group by other columns where you are not using aggregate function:

SELECT Run.TaskName,history.runtime,
   CONVERT(CHAR(10), DATEADD(second, history.runtime, 0), 108)
FROM dbo.history
INNER JOIN Run ON dbo.history.TaskID=Run.TaskID

You are using MAX for runtime so you can do like :

SELECT Run.TaskName,MAX(history.runtime) runtime,
    CONVERT(CHAR(10), DATEADD(second, MAX(history.runtime), 0), 108)
FROM dbo.history
INNER JOIN Run ON dbo.history.TaskID=Run.TaskID
GROUP BY Run.TaskName

It means either you have to remove MAX from runtime or you have to use group by for rest of columns.

edited Sep 19 '16 at 11:31

answered Sep 19 '16 at 11:22

Shushil Bohara

5,556
2
15
32

tx :-) **MAX** is the problem becouse i dont want to group – Thomas Sep 19 '16 at 11:38

score 1 · Answer 2 · answered Sep 19 '16 at 11:25

1

Most probably you needn't MAX. select CONVERT(CHAR(10), DATEADD(second, 60, 0), 108) is OK, so

SELECT Run.TaskName,history.runtime
      ,CONVERT(CHAR(10), DATEADD(second, history.runtime, 0), 108)
FROM dbo.history 
INNER JOIN dbo.Run
    ON dbo.history.TaskID=dbo.Run.TaskID

answered Sep 19 '16 at 11:25

Serg

22,285
5
21
48

i wish i had found Thad myselfe, tx for the grate help – Thomas Sep 19 '16 at 12:30

Tharsan Sivakumar · Answer 3 · 2016-09-19T11:28:05.877

0

Make sure you have necessary permissions to Run table and use the full qualified table as below if the table exists and you have necessary permission to read the tables

SELECT Run.TaskName,history.runtime
      ,CONVERT(CHAR(10), DATEADD(second, MAX(history.runtime), 0), 108)
FROM dbo.history 
INNER JOIN dbo.Run
    ON dbo.history.TaskID=dbo.Run.TaskID

edited Sep 19 '16 at 11:28

answered Sep 19 '16 at 11:22

Tharsan Sivakumar

6,351
3
19
28

SQL query seconds to date (Ssms12)

3 Answers3