Solved

Convert UTC to another Time Zone

Posted on 2007-03-23
8
783 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 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