In this article, we will cover how to convert UTC to the local time zone for the different times of UTC in the database. Now, let’s execute a query that converts the UTC to local time zone using MSSQL as our database in detail step-by-step.
Step 1: Creating a database time_converter by using the following SQL query as follows.
CREATE DATABASE time_converter;
Step 2: Using the database time_converter using the following SQL query as follows.
USE time_converter;
Step3: Creating table times with 2 columns using the following SQL query as follows.
Let us create a table with index and DATETIME as a datatype.
CREATE TABLE times (Sno INT, date_time DATETIME);
Step 4: To view the description of the tables in the database using the following SQL query as follows.
EXEC sp_columns times;
Step 5: Inserting rows into times table using the following SQL query as follows.
INSERT INTO times VALUES (1,'2021-03-01 12:00:00'), (2,'2021-04-01 09:30:00'), (3,'2021-02-05 10:50:00'), (4,'2021-04-18 08:50:00');
Step 6: Viewing the table times after inserting rows by using the following SQL query as follows.
SELECT * FROM times;
Query 1: Query to convert current time from UTC to local IST (Indian Standard Time)
Note: India’s time zone is 5hours and 30 minutes ahead of UTC.
Syntax:
SELECT column_name, CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, column_name), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS general_name
SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETUTCDATE()), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST;
Here, the GETUTCDATE() function can be used to get the current date and time UTC. Using this query the UTC gets converted to local IST.
Query 2: Query to convert all the times in times table to local IST from UTC.
Method 1:
In this method, the UTC times in the table get converted to local Indian standard time. To get the UTC converted to IST, UTC must be added with “+05:30” the same gets added in the output.
Syntax:
SELECT column_name, CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, column_name), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS general_name FROM table_name;
SELECT date_time, CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, date_time), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST FROM times;
Method 2:
In this method, the difference between the present date-time(GETDATE()) and UTC (GETUTCDATE()) date time gets added to the dates in date_time column.
Syntax:
SELECT column_name, DATEADD(MI, DATEDIFF(MI, GETUTCDATE(), GETDATE()), date_time) AS LOCAL_IST FROM times;
SELECT date_time, DATEADD(MI, DATEDIFF(MI, GETUTCDATE(), GETDATE()), date_time) AS LOCAL_IST FROM times;