Solved

Convert UTC to another Time Zone

Posted on 2007-03-23
8
786 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 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