Determine SQL Server instance uptime

SQL Server does not provide an easy and simple query to Determine instance uptime, to achieve the following we can do a trick - tempdb database is being created every time we start SQL Server. Querying the create time of current reincarnation of temporary database will result in the Instance uptime. The table/columns we are interested is sysdatabase.

 

Solution:

declare @startupDate datetime
, @days bigint
, @hours bigint
, @minutes bigint
, @seconds bigint
select @startupDate = CrDate
, @seconds = DateDiff(second, CrDate, getdate())
, @days = @seconds/60/60/24
, @seconds = @seconds - (@days*60*60*24)
, @hours = @seconds/60/60
, @seconds = @seconds - (@hours*60*60)
, @minutes = @seconds/60
, @seconds = @seconds - (@minutes*60)
from sysdatabases (nolock)
where [name] = 'TempDb'
select @startupDate startup_time
, cast(@days as varchar) + ' days ' +
case when @hours < 10 then '0' else '' end + cast(@hours as varchar) + ':' +
case when @minutes < 10 then '0' else '' end + cast(@minutes as varchar) + ':' +
case when @seconds < 10 then '0' else '' end + cast(@seconds as varchar)
as online_duration