SQL Server 2008 Function Convert GMT time to AEST and also take of daylight Saving

Question

I need to convert the time from GMT format to AEST (Australia) and also take care of the daylight saving properly.

Any idea or help will be appreciated.

Thanks Shadab

Answer 1

MySQL provides a CONVERT_TZ function. For this to work, the time_zone% tables have to be populated.

There's no need for me to repeat the MySQL documentation.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

Answer 2

In SQL Server 2008 and newer, you can use the DATETIMEOFFSET datatype for date/time values including a time zone.

There's also a TODATETIMEOFFSET function that converts a UTC time to a timezone-based date/time.

So in your case, you could convert the UTC date/time to AEST date/time like this:

DECLARE @todaysDateTime datetime2;
SET @todaysDateTime = SYSUTCDATETIME();

SELECT 
    TODATETIMEOFFSET (@todaysDateTime, '+10:00'); -- AEST date/time

What I don't really know is whether there's anything to automagically handle daylight savings settings - you might need to handle that yourself, by defining the timezone offset (+10:00 for AEST, +11:00 for AEDT, depending on the date in the year).