How can I convert an int to datetime in SQL Server?

Question

I want to convert an int to datetime in SQL Server. The finished column and the opened column are unix timestamp.

SELECT (finished - opened) as duration FROM results

Output:

duration
---------
19
65
15

For example if the duration is 65, I wanted it to be 00:00:01:05, and if the duration is 15 I want it to be 00:00:00:15. How should I format it?

Possible duplicate of [How can I convert bigint (UNIX timestamp) to datetime in SQL Server?](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) — Masoud Keshavarz, Sep 18 '19 at 04:45
perhaps this is what you want https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql — Antony Gibbs, Sep 18 '19 at 04:55

score 0 · Answer 1 · answered Sep 18 '19 at 05:26

0

SELECT DATEADD(s, 65, 0)

65 is the duration in seconds. Above query will provide 01.01.1900 00:01:05 as output

answered Sep 18 '19 at 05:26

Nevin

score 0 · Answer 2 · answered Sep 18 '19 at 05:27

0

You can use the following (Basically create a time with 1 second and then add the duration-1) in seconds:

select dateadd(second,(finished - opened)-1,timefromparts(0,0,1,0,1))
from results

answered Sep 18 '19 at 05:27

cte6

2 Answers2