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. Pokémon Nut. 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