T-SQL - How to use xp_regread to get ALL time zone information
in [Programming SQL]
|
Prev: "Timeout expired" when querying linked server
Next: The query processor ran out of stack space during query optimization. Please simplify the query.
From: uughaze on 28 Sep 2008 22:43 xp_regread can be used to determine the Time Zone offset from the windows registry. The TZI portion of the example registry key below is of type BINARY. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\AUS Central Standard Time] "Display"="(GMT+09:30) Darwin" "Dlt"="AUS Central Daylight Time" "Std"="AUS Central Standard Time" "MapID"="-1,76" "Index"=dword:000000f5 "TZI"=hex:c6,fd,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\ 00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00 The TZI BIAS from the above can be read as follows:- declare @TZIBias int exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\AUS Central Standard Time','TZI', @ TZIBias OUTPUT What is required is to be able to read the entire TZI information including standard bias, daylight bias, standard date and daylight date. This is represented in VB as the following data type:- Private Type REGTIMEZONEINFORMATION Bias As Long StandardBias As Long DaylightBias As Long StandardDate As SYSTEMTIME DaylightDate As SYSTEMTIME End Type How can T-SQL be used to return the ALL the data components? Thanks, UUG.
From: Michael Coles on 29 Sep 2008 01:08 Are you on SQL 2005 or higher? If so I'd recommend writing a SQL CLR table-valued function to do this for you instead of using the extended stored procedure xp_regread. -- ======== Michael Coles "Pro T-SQL 2008 Programmer's Guide" http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X "uughaze(a)43" <uughaze43(a)discussions.microsoft.com> wrote in message news:605A71DD-754A-4F77-84C5-83BDC3637BCA(a)microsoft.com... > xp_regread can be used to determine the Time Zone offset from the windows > registry. The TZI portion of the example registry key below is of type > BINARY. > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time > Zones\AUS Central Standard Time] > "Display"="(GMT+09:30) Darwin" > "Dlt"="AUS Central Daylight Time" > "Std"="AUS Central Standard Time" > "MapID"="-1,76" > "Index"=dword:000000f5 > "TZI"=hex:c6,fd,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\ > 00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00 > > The TZI BIAS from the above can be read as follows:- > > declare @TZIBias int > exec master.dbo.xp_regread > 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows > NT\CurrentVersion\Time Zones\AUS Central Standard Time','TZI', @ TZIBias > OUTPUT > > What is required is to be able to read the entire TZI information > including > standard bias, daylight bias, standard date and daylight date. This is > represented in VB as the following data type:- > > Private Type REGTIMEZONEINFORMATION > Bias As Long > StandardBias As Long > DaylightBias As Long > StandardDate As SYSTEMTIME > DaylightDate As SYSTEMTIME > End Type > > How can T-SQL be used to return the ALL the data components? > > Thanks, > UUG. > > > > > >
From: uughaze on 29 Sep 2008 01:22 Thanks Michael. Yes, is on SQL 2005. Yes, have considered SQL CLR to do this however wanted to have a solution that was not reliant on deploying CLR components. "Michael Coles" wrote: > Are you on SQL 2005 or higher? If so I'd recommend writing a SQL CLR > table-valued function to do this for you instead of using the extended > stored procedure xp_regread. > > -- > > ======== > Michael Coles > "Pro T-SQL 2008 Programmer's Guide" > http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X > > > "uughaze(a)43" <uughaze43(a)discussions.microsoft.com> wrote in message > news:605A71DD-754A-4F77-84C5-83BDC3637BCA(a)microsoft.com... > > xp_regread can be used to determine the Time Zone offset from the windows > > registry. The TZI portion of the example registry key below is of type > > BINARY. > > > > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time > > Zones\AUS Central Standard Time] > > "Display"="(GMT+09:30) Darwin" > > "Dlt"="AUS Central Daylight Time" > > "Std"="AUS Central Standard Time" > > "MapID"="-1,76" > > "Index"=dword:000000f5 > > "TZI"=hex:c6,fd,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\ > > 00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00 > > > > The TZI BIAS from the above can be read as follows:- > > > > declare @TZIBias int > > exec master.dbo.xp_regread > > 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows > > NT\CurrentVersion\Time Zones\AUS Central Standard Time','TZI', @ TZIBias > > OUTPUT > > > > What is required is to be able to read the entire TZI information > > including > > standard bias, daylight bias, standard date and daylight date. This is > > represented in VB as the following data type:- > > > > Private Type REGTIMEZONEINFORMATION > > Bias As Long > > StandardBias As Long > > DaylightBias As Long > > StandardDate As SYSTEMTIME > > DaylightDate As SYSTEMTIME > > End Type > > > > How can T-SQL be used to return the ALL the data components? > > > > Thanks, > > UUG. > > > > > > > > > > > > > > >
From: Michael Coles on 4 Oct 2008 01:42
The extended stored procedure API is deprecated (for very good reasons). This is why I recommend using SQL CLR instead of XPs, but the final decision is yours. "uughaze(a)43" <uughaze43(a)discussions.microsoft.com> wrote in message news:C7BB25D0-BDC6-4E0E-8A79-ED8661610EDA(a)microsoft.com... > Thanks Michael. > > Yes, is on SQL 2005. > > Yes, have considered SQL CLR to do this however wanted to have a solution > that was not reliant on deploying CLR components. > > "Michael Coles" wrote: > >> Are you on SQL 2005 or higher? If so I'd recommend writing a SQL CLR >> table-valued function to do this for you instead of using the extended >> stored procedure xp_regread. >> >> -- >> >> ======== >> Michael Coles >> "Pro T-SQL 2008 Programmer's Guide" >> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X >> >> >> "uughaze(a)43" <uughaze43(a)discussions.microsoft.com> wrote in message >> news:605A71DD-754A-4F77-84C5-83BDC3637BCA(a)microsoft.com... >> > xp_regread can be used to determine the Time Zone offset from the >> > windows >> > registry. The TZI portion of the example registry key below is of >> > type >> > BINARY. >> > >> > HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time >> > Zones\AUS Central Standard Time] >> > "Display"="(GMT+09:30) Darwin" >> > "Dlt"="AUS Central Daylight Time" >> > "Std"="AUS Central Standard Time" >> > "MapID"="-1,76" >> > "Index"=dword:000000f5 >> > "TZI"=hex:c6,fd,ff,ff,00,00,00,00,c4,ff,ff,ff,00,00,00,00,00,00,00,00,00,00,00,\ >> > 00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00,00 >> > >> > The TZI BIAS from the above can be read as follows:- >> > >> > declare @TZIBias int >> > exec master.dbo.xp_regread >> > 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows >> > NT\CurrentVersion\Time Zones\AUS Central Standard Time','TZI', @ >> > TZIBias >> > OUTPUT >> > >> > What is required is to be able to read the entire TZI information >> > including >> > standard bias, daylight bias, standard date and daylight date. This is >> > represented in VB as the following data type:- >> > >> > Private Type REGTIMEZONEINFORMATION >> > Bias As Long >> > StandardBias As Long >> > DaylightBias As Long >> > StandardDate As SYSTEMTIME >> > DaylightDate As SYSTEMTIME >> > End Type >> > >> > How can T-SQL be used to return the ALL the data components? >> > >> > Thanks, >> > UUG. >> > >> > >> > >> > >> > >> > >> >> >> |