Solved

Convert UTC to another Time Zone

Posted on 2007-03-23
8
793 Views
Last Modified: 2008-01-09
I have a field I'm pulling from SQL that is in UTC.  I am pulling it into a Gridview (asp.net 2.0 VB)  but I want it to display as CST not UTC.  How do I do that.  Do I need to do a conversion when I call the data from sql or is there something I can do in VB?  
0
Comment
Question by:intchauspe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18782592
i think better you hadle this at the  front end .
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 18782675
How dynamic do you want this to be?  If you want to use whatever time zone the server is in you could use this:

Dim sqlDate as DateTime = ' get data from database
' Sepcify that the time is UTC time
sqlDate = DateTime.SpecifyKind(sqlDate, DateTimeKind.Utc)
' Convert the time to local time
Dim localDate as DateTime = sqlDate.ToLocalTime()

Unfortunately there's not a built-in construct for displaying a time in a particular time zone.  You'd have to build a collection of time zones and offsets, then add the particular offset to the time. (e.g. for CST you'd subtract 6 hours)
0
 
LVL 10

Expert Comment

by:lahousden
ID: 18782694
We couldn't find a 3rd party solution so we have implemented this in our application.  Briefly, though, you shouldn't need much more than a table which holds:
a) a time zone name
b) the number of minutes to be added to a UTC time to convert it to that time zone (bear in mind that for time zones West of Greenwich, England this will be a negative number) - you should probably have two such columns, one for the number of minutes for Standard TIme and one for the number of minutes for Daylight Time in that time zone.

You may want to store the exact date and time (in UTC time) at which Daylight Saving kicks in and kicks out for that time zone each year - this will have to be a separate table, of course.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18782983
If you are in CST and only care about CST than you may be able to do something like this:
Select DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), YourUTCColumn) YourLocalTime
From YourTableName

Of course, this would be best encapsulated in a function, but your get the idea.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18782999
>>Of course, this would be best encapsulated in a function, but your get the idea.<<
Of course you cannot use GETDATE() and GETUTCDATE() in a UDF
0
 

Author Comment

by:intchauspe
ID: 18792774
Little help..... This is what I have so far... I'm having trouble with the DATEADD part.

SELECT     nvarchar1, nvarchar2, nvarchar3, datetime1, datetime2, ntext1, ntext2, ntext3,
GETUTCDATE() AS utc_time, GETDATE() AS lcl_time, DATEDIFF(hour, GetUTCDate(), GETDATE()) AS time_zone
FROM         dbo.UserData
0
 

Author Comment

by:intchauspe
ID: 18793781
oh, got it.... thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794488
Incidentally and for the record, the reason time differences are measured in minutes and not hours in Windows API structures is that some time zones offsets have fractions of hours.  In other words they may be +7:30 hours GMT. This is why some of the suggestions here in this thread used minutes and not hours.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question