Time conversion from local to UTC and vice-versa
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.