Not unsurprisingly SQL Azure GetDate() stores datetime as a UTC value.
If your application is hosted in a specific country it is likely you will wish to return that value to the local user in the local time.
Here I host in the UK and want to display in the UK so my choice is simple enough.
The default CONVERT() statement in the SQL Azure library will do this for you.
An example of the statement with parameters is here (slightly confusingly here I have the field name as a reserved word but just ensure that your field name is in the square brackets)
CONVERT(datetime, SWITCHOFFSET([datetime], DATEPART(TZOFFSET, [datetime] AT TIME ZONE 'GMT Standard Time'))) as UKTime
And here is that statement included in a full query.
And here is an SQL statement to determine all time zones currently observing daylight saving on your server.
This query can also be used to identify the pre-defined text string parameter you should have for your convert statement.
SELECT name, current_utc_offset FROM sys.time_zone_info WHERE is_currently_dst = 1;
Running this I get the following..
Now what is interesting about this is that the text string defined by Microsoft is GMT Standard Time and according to my investigations it definitely adjusts for British Summer Time (which most of the time is what I want!). OK but if you go to Wikipedia you get the following definition of what GMT is :-
Greenwich Mean Time or GMT is the time displayed by the Shepherd Gate Clock at the Royal Observatory in Greenwich, London. When the sun is at its highest point exactly above the Prime Meridian, it is 12:00 noon at Greenwich.
GMT is not affected by Daylight Saving Time (DST) clock changes.
The Greenwich Meridian (Prime Meridian or Longitude Zero degrees) is the reference point for every Time Zone in the world.Every 15° longitude represents one hour’s difference in time: (24 x 15 = 360, the degrees of a circle). You can work out the time at every location on earth if you know how many degrees it is east or west of Greenwich.
GMT is not affected by Daylight Saving Time (DST) clock change -> So obviously that contradicts the definition in SQL Server – fair play to the engineer who set up the sys.table he just wasn’t aware. Probably won’t be changed now but its indicative of the danger there is in dealing with time when you need to be ultra accurate. Users tend to think that dealing with time is straight forward but in reality its not!
