I recently had to do some investigating on SQL w/r/t start-up times. Some of this stuff is probably frequently asked on the internet, and the last query where i want the up-time of the instance in hours minutes and seconds took a bit of trial and error:


--Q. How Do I Get the Current date time on my SQL Server using SSMS/T-SQL?
--A: Select the getdate function
SELECT GETDATE()
GO

--Q. How do I get the time only on my SQL Server using SSMS/T-SQL?
--A: Simple select and convert the getdate to time type
SELECT CONVERT(TIME,GETDATE()) AS Time;
GO

--Q. How do I get the date only of my SQL Server Instance using SSMS/TSQL?
--A: Simple select and convert the getdate to date type
SELECT CONVERT(DATE,GETDATE()) AS Date;
GO

--Q. How do I get the start time of my SQL Server Instance using SSMS/T-SQL?
--A: Good old sys tables
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

--Q. How do I get the duration of up-time in hours/minutes/seconds?
--A: We get the difference in hours minutes and seconds from setting two variables the values from the above examples
DECLARE @StartUpTime DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info)
,@CurrentDate DATETIME = (SELECT GETDATE())

SELECT DATEDIFF (dd, @StartUpTime, @CurrentDate) AS [DAYS]
,DATEDIFF (hh, @StartUpTime, @CurrentDate) % 24 AS [HOURS]
,DATEDIFF (mi, @StartUpTime, @CurrentDate) % 60 AS [MINUTES]
,DATEDIFF (ss, @StartUpTime, @CurrentDate) % 60 AS [SECONDS]