Time conversion from local to UTC and vice-versa

Spread the love

Recently I have been working on setting up baselining for my SQL Server lab environment. As part of baselining, I capture all the required Perfmon counters both at SQL Server and OS.

But, Grafana requires time in UTC format. Since all the data collection in my case was in local server time using GETDATE(), I had to write below 2 functions in order to convert server time to UTC and vice-versa.

use DBA
go

create function dbo.utc2local (@utc_datetime datetime2)
returns datetime2 with schemabinding
as
begin
	declare @local_time datetime2;
	select @local_time = Dateadd(MILLISECOND, Datediff(MILLISECOND, Getutcdate(), Getdate()), @utc_datetime);

	return (@local_time);
end
go

create function dbo.local2utc (@local_datetime datetime2)
returns datetime2 with schemabinding
as
begin
	declare @utc_time datetime2;
	select @utc_time = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @local_datetime);

	return (@utc_time);
end
go

I hope this would be helpful to anyone having similar use case like me.

Happy coding 🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.