Your SQL questions, answered

How to convert UTC to LOCAL time conversion in SQL Server – Querychat

We often come across situations to convert date and time to any other time zone for end users convenience. Time zone conversion becomes very important when we have users from all over the globe, to make day to day analysis smooth and uniform all over the globe.

In this article, we will learn various ways of time zone conversion in SQL Server with syntax and examples.

TODATETIMEOFFSET in SQL Server

The TODATETIMEOFFSET function converts the datetime2 value to the specified time zone.

Here we can specify the target time zone using its minute and hours relative to the UTC.

Syntax

todatetimeoffset ( input_expression, time_zone ) 

Todatetimeoffset Syntax

Arguments:

TODATETIMEOFFSET Examples

1. Change the HOUR: MINUTES offset of current date time

DECLARE @CurrentDateTime DATETIME2; 

SET @CurrentDateTime = Getdate(); 

SELECT Todatetimeoffset (@CurrentDateTime, '+08:00') AS 

       CURRENT_DATE_TIME_OFFSET_CHANGE;

Todatetimeoffset Hour-Minute Offset Change Example 

2. Change the only minutes offset of current date time

SELECT Todatetimeoffset(Sysdatetime(), -90) 

Todatetimeoffset Minute Offset Change Example

SQL Server convert UTC to Local time conversion

Using dateadd(), datediff(), getutcdate() and getdate() functions we can convert UTC DateTime to LOCAL time.

DATEADD(): This function adds the value to the specified date part in the input DateTime.

Syntax:

DATEADD (date_part , value_to_add , input_date ) 

DATEADD Function Syntax

Date part could be a year(yy, yyyy), quarter(qq, q), month(n, mm), day(d, dd), week(wk, ww), day of year(y, dy), hour(hh), minute(mi, n), second(s, ss), millisecond(ms), microsecond(mcs), and nanosecond(ns).

DATEDIFF(): This function returns the difference between two dates with respect to the specified interval.

Syntax:

DATEDIFF( interval, input date1, input  date2 )

DATEDIFF Function Syntax

Interval used to calculate the difference between two dates.

Interval could be a year(yy, yyyy), quarter(qq, q), month(n, mm), day(d, dd), week(wk, ww), day of year(y, dy), hour(hh), minute(mi, n), second(s, ss), millisecond(ms), microsecond(mcs), and nanosecond(ns).

GETUTCDATE(): This function returns the current UTC date and time.  GETUTCDATE function does not have any parameter. It returns the date and time in  ‘yyyy-mm-dd hh:mi:ss.mmm format.

GETDATE():  This function returns the current date and time in  ‘YYYY-MM-DD hh:mm:ss.mmm’ format.

DECLARE @utcdate DATETIME = '2019-07-14 11:11:37.127' 

SELECT @utcdate AS UTCTime, Dateadd(hh, Datediff(hh, Getutcdate(), Getdate()), @utcdate) AS LocalTime;  

UTC Time to LOCAL Time Conversion Example

Fig.1 UTC Time to LOCAL Time Conversion Example

UTC to LOCAL Time Conversion Step By Step Explanation

Step-1: Get the current LOCAL DateTime and current UTC DateTime

Step-2: Find the difference between the current UTC and LOCAL DateTime in hours using the DATEDIFF function.

Step-3: Then using DATEADD function add the #step-2 difference to input date.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc