SQL Server full time zone conversion

SQL Server full time zone conversion

If your application is used across borders (such as cross-border bank transactions), then some of the internationalization features of the database can be said to be very important.

The most common of these are the differences in time zones, because SQL Server getdate () reads the values returned by the local machine.

does not contain database time zone displacements, SQL Server2008 new DateTimeOffset data type (time of day based on time zone)

With the Switchoffset function to more easily handle time zone conversion problems

--Time zone ConversionSELECTSysdatetimeoffset () as"Taipei (GMT+8:xx) ", Switchoffset (Sysdatetimeoffset (),'+09:00') as"Janpen (GMT+9:xx) ", Switchoffset (Sysdatetimeoffset (),'-10:00') as"Hawaii (GMT-Ten:xx)"

-- get the time zone shift in Taipei Select DATEDIFF (MI, Sysdatetimeoffset (),getdateas'timezoneoffset' )

480 min/60 min=8 HR

--Read Job system login get GMT and DST namesDECLARE @GMTname VARCHAR( -),@DSTname VARCHAR( -); EXECXp_regread "HKEY_LOCAL_MACHINE", "SYSTEM\CurrentControlSet\Control\TimeZoneInformation", "StandardName",@GMTnameOUTPUTEXECXp_regread "HKEY_LOCAL_MACHINE", "SYSTEM\CurrentControlSet\Control\TimeZoneInformation", "DaylightName",@DSTnameOUTPUTSELECT @GMTname  as 'GMT (Gmt:greenwich Mean time)',@DSTname  as 'Daylight Saving time (Dst:daylight saving time)'

Reprinted from: Http://www.dotblogs.com.tw/ricochen/archive/2012/10/30/79754.aspx

SQL Server full time zone conversion