How to Convert Dates to Local Time in Your SCSM Queries

Author by Christopher Mank

In a previous blog post, we went over how Service Manager stores its dates and times in UTC format.  This is super helpful to know when querying the database and/or writing reports.  Now having this knowledge is all well and good, but when we want to look at the data in the tables, it can quickly become cumbersome to try and convert the dates in our heads to actual date/times that make sense.  This is especially true when troubleshooting a performance issue and knowing the precise time of an event is critical. To help alleviate this issue, I went ahead and created a quick and simple SQL function that will convert any UTC date to your actual date.  Let's take a look. The query below is finding the last 100 transactions in the CMDB by querying the EntityTransactionLog table.  The most recent event was created by me at 9:22PM CDT.  As you notice, since CDT is -5 from UTC, the date shows in the database as 2:22AM the next day. QueryWithoutFunction Image But when we are troubleshooting issues, it's kind of nice to just see the correct time so that we don't have to constantly convert the date/time in our heads.  So, below is a SQL command you can run that will create a new SQL function to do this for you. GetLocalTime Image Basically what this function is doing is taking in one parameter, which is the UTC date you wish to convert.  It then finds the hour difference between your local time and UTC time.  Using that hour difference, we can calculate and return the localized date and time.  To add this function to your environment, simply run the CREATE FUNCTION command you see here (copy/paste mode found below). So here is our same query from above, but now utilizing our new GetLocalTime function.  As you can see, it now shows the correct time of 21:22, which translates to 9:22PM. QueryWithFunction Image If you wish to convert the date even more, perhaps to 12Hour time, you can follow some of the commands listed here to convert the data even further (article found here). CREATE FUNCTION in Copy/Paste Mode: USE [ServiceManager] GO /****** Object: UserDefinedFunction [dbo].[fn_GetLocalTime] Script Date: 10/31/2013 21:18:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Christopher Mank -- Create date: 10/21/2013 -- Description: Converts UTC datetime to local datetime -- ============================================= CREATE FUNCTION [dbo].[fn_GetLocalTime] (@UtcDateToConvert DATETIME) RETURNS DATETIME AS BEGIN DECLARE @UtcDate DATETIME DECLARE @LocalDate DATETIME DECLARE @TimeDiff INT DECLARE @ConvertedLocalDate DATETIME SET @UtcDate = GETUTCDATE() SET @LocalDate = GETDATE() SET @TimeDiff = DATEDIFF(hh, @UtcDate, @LocalDate) RETURN DATEADD(hh, @TimeDiff, @UtcDateToConvert) END GO I hope this helps those wanting to convert their UTC dates. Until the Whole World Hears, Christopher

Christopher Mank

Systems Architect