December 14, 2011 at 5:34 am
Guys, we're trying to ascertain the GMT off set for a particular country and or state and then be able to extract these with the location fields to help us knowing the best time to contact certain entities.
I've thought I could simply build a lookup table based on countries and states and then get a table of time zones from Google somewhere, however, is there a more sophisticated approach that could be a bit more intelligent? - For example dealing with daylight saving in various locations for starters.
Not sure if anyone else has encountered this issue and what solutions you may have come up with?
The end goal is to use a function which would return the offset for a given country and or state basically.
Thoughts?!
December 14, 2011 at 5:40 am
I think you're on the right track. I think I'd have a timezone table and a daylight savings table as lookups, rather than just a timezone table.
December 14, 2011 at 5:42 am
Offset is Daylight saving unaware. A lookup table with timezone & daylight saving would be a good idea.
December 14, 2011 at 5:44 am
Hi Could you not just use a datetimeoffset and substring off the timezone.
Or am i missing your point entirely?
December 14, 2011 at 5:48 am
I guess the point is I was wondering if there was a better approach than looking up every country and every state and compiling an offset table and then using this to say 'Canada' is X hours away from GMT, NY is Y hours away etc.
I didn't know if there was something smart you could do if say you had the coordinates stored for the time zone if you could fetch them from the web or something?!
Think I'm getting ahead of myself a little there though.
December 14, 2011 at 5:53 am
Rob-350472 (12/14/2011)
I guess the point is I was wondering if there was a better approach than looking up every country and every state and compiling an offset table and then using this to say 'Canada' is X hours away from GMT, NY is Y hours away etc.I didn't know if there was something smart you could do if say you had the coordinates stored for the time zone if you could fetch them from the web or something?!
Think I'm getting ahead of myself a little there though.
I am afraid you are choosing a rough path for yourself. Creating a lookup table is one time exercise & you can use it for all applications in your organizations.
December 14, 2011 at 5:55 am
If your trying to get the country from the datestamp this will be hard as many countrys resisde in the same zones.
Also if you are trying to get the Zone from the country then you can use the SELECT SYSDATETIMEOFFSET() which will return the local computers timezone.
as long as your pc takes into account daylight savings this will be picked up too.
if you are stamping records with the timezone they are in then i would advise getting the application (or db) inserting the time to look up the timezone based on a lookup and stick it together. You will have to use a lookup here.
Not sure if Microsoft provide this in .net for you
December 14, 2011 at 6:02 am
It's not the country from the time zone, it's simply an 'address' table if you like - which will have country, and if applicable state. From this I'd simply like to return the GMT offset - so if it's country = 'US' state = 'NY' it's going to be -5 for example.
I'd resigned myself to the look route, I just thought I'd check for a better/neater way of doing it!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply