Solved

Convert UTC to another Time Zone

Posted on 2007-03-23
8
781 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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