intchauspe
asked on
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 (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?
Aneesh
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(sqlDa
' 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)
Dim sqlDate as DateTime = ' get data from database
' Sepcify that the time is UTC time
sqlDate = DateTime.SpecifyKind(sqlDa
' 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)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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
Of course you cannot use GETDATE() and GETUTCDATE() in a UDF
ASKER
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
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
ASKER
oh, got it.... thanks
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.