Convert UTC to another Time Zone

I have a field I'm pulling from SQL that is in UTC.  I am pulling it into a Gridview ( 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?  
Who is Participating?
Anthony PerkinsConnect With a Mentor Commented:
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.
Aneesh RetnakaranDatabase AdministratorCommented:
i think better you hadle this at the  front end .
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)
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.
Anthony PerkinsCommented:
>>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
intchauspeAuthor Commented:
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
intchauspeAuthor Commented:
oh, got it.... thanks
Anthony PerkinsCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.