Date and Start Up Time using T-SQL

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]

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s