Convert Datetime Column from Utc to Local Time in Select Statement

Convert Datetime column from UTC to local time in select statement

You can do this as follows on SQL Server 2008 or greater:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

You can also do the less verbose:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

Whatever you do, do not use - to subtract dates, because the operation is not atomic, and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times (i.e., non-atomically).

Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:

How to create Daylight Savings time Start and End function in SQL Server

Conversion of UTC time to LOCAL in SQL Query output

First, understand that getdate() returns the local date and time of the server - not of the person running the query. If the server's time zone is set to UTC, then it will indeed return the UTC time.

Since you are running on SQL 2016, and you are asking for the UTC time converted to Pacific time, I suggest you use the built-in AT TIME ZONE statement, as follows:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Note, the above assumes that the [Timestamp] field is a datetime or datetime2, and that it is a UTC-based value. The first AT TIME ZONE makes an assertion that the given value is in UTC, resulting in a datetimeoffset. The second AT TIME ZONE then converts from UTC to Pacific time.

If instead the field is already a datetimeoffset type, then the query is even simpler:

SELECT [Id]
      ,[BaseSource]
      ,[Source]
      ,[Output]
      ,[SessionId]
      ,[Timestamp]
      ,[Timestamp] AT TIME ZONE 'Pacific Standard Time' AS LocalTime
FROM [MYDB].[dbo].[SystemOutput]
ORDER BY [Timestamp] DESC

Also, don't be confused by the word "Standard" in the time zone identifier. That value covers both standard time and daylight time, as applicable in the Pacific time zone in the US and Canada.

Convert date column to new UTC date column

Please try this:

DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), datetime)

• The GETDATE() will return the current time
• The GETUTCDATE() will retunr the current UTC time
• The DATEDIFF function will calculate the difference between this two datetimes in hours
• The DATEADD function will add this hours to your current datetime.

Another way is via usign the AT TIME ZONE

SELECT datetime AT TIME ZONE 'UTC' from table1

You can check your time zone with:

select CURRENT_TIMEZONE ( )  

And use this:

SELECT datetime  AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' 
from table1

If you are in 'Pacific Standard Time' timezone

How to convert from UTC to EST in SQL?

CAST(sample_start_time AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME2) AS sample_start_time_est

Oracle - Convert UTC Date to Local Date Time US Format

Here is a step-by-step of the process

 I will start with a timestamp in UTC

    SQL> select timestamp '2021-07-01 09:00:00 +00:00' ts from dual;
    
    TS
    ---------------------------------------------------------------------
    01-JUL-21 09.00.00.000000000 AM +00:00

Assuming this is stored in some sort of table, the first thing we can do is convert it to the desired time zone. In my case, I live in Perth, Australia so I do:

    SQL> with my_table as (
      2  select timestamp '2021-07-01 09:00:00 +00:00' ts from dual
      3  )
      4  select ts at time zone 'Australia/Perth' as perth_time
      5  from my_table;
    
    PERTH_TIME
    ---------------------------------------------------------------
    01-JUL-21 05.00.00.000000000 PM AUSTRALIA/PERTH

9am UTC is 5pm in Perth (we're 8 hours in front).  Now I want that output in a format that I want, so I can TO_CHAR that in the normal way

SQL> with my_table as (
  2  select timestamp '2021-07-01 09:00:00 +00:00' ts from dual
  3  )
  4  select to_char(ts at time zone 'Australia/Perth','MM/DD/YYYY HH24:MI:SS') as perth_time
  5  from my_table;

PERTH_TIME
-------------------
07/01/2021 17:00:00

and we're done

How to Convert UTC Date To Local time Zone in MySql Select Query

SELECT CONVERT_TZ() will work for that.but its not working for me.

Why, what error do you get?

SELECT CONVERT_TZ(displaytime,'GMT','MET');

should work if your column type is timestamp, or date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

Test how this works:

SELECT CONVERT_TZ(a_ad_display.displaytime,'+00:00','+04:00');

Check your timezone-table

SELECT * FROM mysql.time_zone;
SELECT * FROM mysql.time_zone_name;

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

If those tables are empty, you have not initialized your timezone tables. According to link above you can use mysql_tzinfo_to_sql program to load the Time Zone Tables. Please try this

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo

or if not working read more: http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html

---

Related Topics