Using T-SQL Convert LOCAL datetime to UTC datetime value using UTC offset (defined)

While working on some things recently, I figured there is an easy way to convert (using T-SQL) local time to UTC departure time … and that is leveraging built-in DATEADD function  

Reference
http://technet.microsoft.com/en-us/library/ms186819.aspx

The key is using the right offset value for calculating UTC .. in the scenario that we dealt with, the database has Offset defined in minutes and is based on the offset/difference in minutes FROM UTC time so to convert the captured local date time to UTC.. the following works just fine ..

DATEADD(mi, - <UTCOffsetValue>, <LOCALDateCaptured>)    // mi - minutes

As an example for offset, Canada EST (with Daylight) has the value for the Offset defined as -300 [5 hours behind of UTC] whereas for France it is defined as + 60 [1 hour ahead of UTC]

Comments

Post a Comment

Popular posts from this blog

Baseless merge!

Baseless merge in TFS ? or a Baseless Question? :-) A colleague came over wondering for what someone asked him to do - and that is to merge two branches. I believe, the tool allows you to do that however, I wouldn't recommend it simply because it is a sort of sibling-sibling merge rather than (what it is intended for) parent-child merge. Reference to support my perspective ... How do I merge two branches? To perform a merge, you can use the merge functionality in Source Control Explorer, or you can use the  merge  command-line tool. You can merge based on changeset, label, date, or version. You might find that your branching structure becomes deeper than one level. If this happens, keep in mind that it is only possible to merge one level at a time. For example, consider the following source tree structure: Development  – Container for isolation development branches. Feature A Source Feature B Source Main  – Main integration build bran...
Read more

QuadKey - what it is?

Image
To start with, let me share an excerpt from MSDN as below: Bing Maps Tile System (Reference:   https://msdn.microsoft.com/en-us/library/bb259689.aspx ) Bing Maps provides a world map that users can directly manipulate to pan and zoom. To make this interaction as fast and responsive as possible, we chose to pre-render the map at many different levels of detail, and to cut each map into tiles for quick retrieval and display. This document describes the projection, coordinate systems, and addressing scheme of the map tiles, which collectively are called the Bing Maps Tile System. So if you follow the above references Bing Maps Tile System, you would get an idea that  - how the world map is kind of apportioned into a tile system and depending on what level of detail or zoom you are interested in, you would get a <N> character long quad-key.  For what I am involved with currently, we care to go for the maximum allowable detail to uniquely identify each...
Read more

Binding ENUM to WPF control - ComboBox

I believe, I explored quite a bit on finding the best approach for my requirement of binding Enum to WPF ComboBox and thought to share the gradual steps here for a one-stop place which hopefully will help similar minded technical enthusiast .. The simplest approach: You want to just bind Enum as it appears (verbatim) to the WPF combo .. Let's define a sample enum as follows: namespace {    public enum TestEnum    {       String1,       String2,       String3,       String4    } } All we now need is a way to leverage Enum - GetValues method to provide a list of values that lets you bind directly to the ComboBox via ItemSource. < ObjectDataProvider x : Key ="dataFromEnum" MethodName ="GetValues" ObjectType ="{ x : Type sys : Enum }" > < ObjectDataProvider.MethodParameters > < x : Type TypeName ="local:TestEnum" /> </ ...
Read more