|
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Checking the new DST 2007 from a</title>
<style>
<!--
div.Section1
{page:Section1;}
div.Section2
{page:Section2;}
p.MsoNormal
{mso-style-parent:"";
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";
margin-left:0in; margin-right:0in; margin-top:0in}
p.MsoToc1
{margin-bottom:.0001pt;
tab-stops:24.0pt right dotted 467.5pt;
font-size:12.0pt;
font-family:"Times New Roman";
font-weight:bold;
margin-left:0in; margin-right:0in; margin-top:0in}
span.MsoHyperlink
{color:blue;
text-decoration:underline;
text-underline:single;}
h1
{margin-top:12.0pt;
margin-right:0in;
margin-bottom:6.0pt;
margin-left:0in;
text-indent:0in;
page-break-after:avoid;
tab-stops:.3in;
punctuation-wrap:simple;
text-autospace:none;
font-size:14.0pt;
font-family:"Arial Bold";
text-transform:uppercase;
}
p.MsoBodyText
{margin-top:0in;
margin-right:0in;
margin-bottom:6.0pt;
margin-left:0in;
punctuation-wrap:simple;
text-autospace:none;
font-size:12.0pt;
font-family:"Times New Roman";
}
table.MsoTableGrid
{border:1.0pt solid windowtext;
font-size:10.0pt;
font-family:"Times New Roman";
}
-->
</style>
</head>
<body>
<div class="Section1">
</div>
<span lang="EN-CA" style="font-size: 12.0pt; font-family: Times New Roman">
<br clear="all" style="page-break-before: always">
</span><div class="Section2">
<p class="MsoNormal" style="text-align: justify"><span lang="EN-CA"> </span></p>
<p class="MsoNormal" align="center" style="text-align: center"><b>
<span lang="EN-CA" style="font-size: 20.0pt">Checking the new DST 2007 from
a DBA Perspective</span></b></p>
<p class="MsoNormal" align="center" style="text-align: center"><b>
<span lang="EN-CA" style="font-size: 20.0pt"> </span></b></div>
<h1 style="text-indent: 0in; margin-left: 0in"><a name="_Toc158009262">1.<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Preamble</a></h1>
<p class="MsoBodyText">As a result of the Energy Policy Act that was passed in
August 2005 by the United States Congress, there is a change in the daylight
saving time starting from 2007 as follows:</p>
<p class="MsoBodyText"> </p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .1in" id="table1">
<tr>
<td width="157" valign="top" style="width: 117.9pt; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #D9D9D9">
<p class="MsoBodyText"><span style="font-size: 10.0pt">Previous DST
(begin)</span></td>
<td width="156" valign="top" style="width: 117.0pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #D9D9D9">
<p class="MsoBodyText"><span style="font-size: 10.0pt">New DST (begin)</span></td>
<td width="168" valign="top" style="width: 1.75in; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #D9D9D9">
<p class="MsoBodyText"><span style="font-size: 10.0pt">Previous DST
(end)</span></td>
<td width="157" valign="top" style="width: 117.9pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: 1.0pt solid windowtext; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #D9D9D9">
<p class="MsoBodyText"><span style="font-size: 10.0pt">New DST (end)</span></td>
</tr>
<tr>
<td width="157" valign="top" style="width: 117.9pt; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">1<sup>st</sup>
Sunday of April</span></td>
<td width="156" valign="top" style="width: 117.0pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">2<sup>nd</sup>
Sunday of March</span></td>
<td width="168" valign="top" style="width: 1.75in; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">Last Sunday of
October</span></td>
<td width="157" valign="top" style="width: 117.9pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">1<sup>st</sup>
Sunday of November</span></td>
</tr>
<tr>
<td width="157" valign="top" style="width: 117.9pt; border-left: 1.0pt solid windowtext; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">April 1, 2007</span></td>
<td width="156" valign="top" style="width: 117.0pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #3366FF">
<p class="MsoBodyText"><span style="font-size: 10.0pt">March 11, 2007</span></td>
<td width="168" valign="top" style="width: 1.75in; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText"><span style="font-size: 10.0pt">October 28, 2007</span></td>
<td width="157" valign="top" style="width: 117.9pt; border-left: medium none; border-right: 1.0pt solid windowtext; border-top: medium none; border-bottom: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in; background: #3366FF">
<p class="MsoBodyText"><span style="font-size: 10.0pt">November 4, 2007</span></td>
</tr>
</table>
<p class="MsoBodyText"> </p>
<p class="MsoBodyText">This document shows you how to check if your MS SQL
Server 2005 is ready for the new DST 2007.</p>
<h1 style="text-indent: 0in; margin-left: 0in"><a name="_Toc158009263">2.<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Technical details</a></h1>
<p class="MsoBodyText">The managed assembly ConvertTime.dll contains 1 stored
procedure (ufnDisplayUTC) and 2 functions (ufnToLTime, and ufnToUTime) as
follows:</p>
<p class="MsoBodyText" style="text-indent: -.25in; margin-left: .5in">
<span style="font-family: Symbol">�<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span></span>ufnDisplayUTC displays information about the current DST. For
example:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .6in" id="table2">
<tr>
<td width="487" valign="top" style="width: 365.4pt; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New"> </span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
Timezone: . . . . . . Eastern Standard Time</span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
New DST for 2007: . . 2007-03-11 02:00 to 2007-11-04 02:00 (01:00:00)</span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
Current time: . . . . 1/31/2007 11:30:32 AM</span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
Universal time: . . . 1/31/2007 4:30:32 PM</span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
UTC offset: . . . . . -05:00:00</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
Daylight saving? . . No</span></td>
</tr>
</table>
<p class="MsoBodyText"> </p>
<p class="MsoBodyText" style="text-indent: -.25in; margin-left: .5in">
<span style="font-family: Symbol">�<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span></span>ufnToUTime(@strDateTime) returns the universal date and time of a
given local date and time. For example:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .6in" id="table3">
<tr>
<td width="490" valign="top" style="width: 5.1in; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select</span><span style="font-size: 8.0pt; font-family: Courier New">
dbo<span style="color: gray">.</span>ufnToUTime<span style="color: gray">(</span><span style="color: red">'2007-03-11
02:00:00.000'</span><span style="color: gray">)</span></span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: gray"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: green">
--3/11/2007 7:00:00 AM</span></td>
</tr>
</table>
<p class="MsoBodyText"> </p>
<p class="MsoBodyText" style="text-indent: -.25in; margin-left: .5in">
<span style="font-family: Symbol">�<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span></span>ufnToLTime(@strDateTime) returns the local date and time of a
given universal date and time. For example:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .6in" id="table4">
<tr>
<td width="490" valign="top" style="width: 5.1in; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select</span><span style="font-size: 8.0pt; font-family: Courier New">
dbo<span style="color: gray">.</span>ufnToLTime<span style="color: gray">(</span><span style="color: red">'2007-03-11
07:00:00.000'</span><span style="color: gray">)</span></span></p>
<p class="MsoNormal" style="text-autospace: none">
<span style="font-size: 8.0pt; font-family: Courier New; color: gray"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: green">
--3/11/2007 3:00:00 AM</span></td>
</tr>
</table>
<h1> </h1>
<span style="font-size: 14.0pt; font-family: Arial Bold">
<br clear="all" style="page-break-before: always">
</span><h1 style="text-indent: 0in; margin-left: 0in">
<a name="_Toc158009264">3.<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>implementation</a></h1>
<p class="MsoBodyText">Execute the following script within the Microsoft SQL
Server Management Studio for a database such as AdventureWorks:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .1in" id="table5">
<tr>
<td width="638" valign="top" style="width: 6.65in; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
CREATE ASSEMBLY [ConvertTime]</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
FROM 'ConvertTime.dll'</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
WITH PERMISSION_SET = SAFE</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">GO</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
CREATE FUNCTION [dbo].[ufnToLTime](@strDateTime [nvarchar](4000))</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">AS
EXTERNAL NAME
[ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnToLTime]</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">GO</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
CREATE FUNCTION [dbo].[ufnToUTime](@strDateTime [nvarchar](4000))</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">AS
EXTERNAL NAME
[ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnToUTime]</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">GO</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
CREATE PROCEDURE [dbo].[ufnDisplayDST]</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">AS</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
EXTERNAL NAME [ConvertTime].[MCSCS.SQL2005.UserDefinedFunctions].[ufnDisplayDST]</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">GO</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></td>
</tr>
</table>
<h1 style="text-indent: 0in; margin-left: 0in"><a name="_Toc158009265">4.<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>testing</a></h1>
<p class="MsoBodyText">If your server <u>has not</u> been patched with the new
DST 2007, you will observe the following result:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .1in" id="table6">
<tr>
<td width="638" valign="top" style="width: 6.65in; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select dbo.ufnToUTime('2007-03-11 02:00:00.000')</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
--3/11/2007 7:00:00 AM</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select dbo.ufnToUTime('2007-03-11 03:00:00.000')</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
--3/11/2007 8:00:00 AM</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></td>
</tr>
</table>
<p class="MsoBodyText"> </p>
<span style="font-size: 12.0pt; font-family: Times New Roman">
<br clear="all" style="page-break-before: always">
</span><p class="MsoBodyText">However, if your server <u>has</u> been
patched with the new DST 2007, you will observe the following result:</p>
<table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse: collapse; border: medium none; margin-left: .1in" id="table7">
<tr>
<td width="638" valign="top" style="width: 6.65in; border: 1.0pt solid windowtext; padding-left: 5.4pt; padding-right: 5.4pt; padding-top: 0in; padding-bottom: 0in">
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select dbo.ufnToUTime('2007-03-11 02:00:00.000')</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
--3/11/2007 7:00:00 AM</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
select dbo.ufnToUTime('2007-03-11 03:00:00.000')</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue">
--3/11/2007 7:00:00 AM</span></p>
<p class="MsoBodyText">
<span style="font-size: 8.0pt; font-family: Courier New; color: blue"> </span></td>
</tr>
</table>
<h1 style="text-indent: 0in; margin-left: 0in"><a name="_Toc158009266">5.<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>conclusion</a></h1>
<p class="MsoBodyText">For more information on how to patch your server, please
check out the following url:
<a style="color: blue; text-decoration: underline; text-underline: single" href="http://www.microsoft.com/windows/timezone/dst2007.mspx">
http://www.microsoft.com/windows/timezone/dst2007.mspx</a> ■</p>
</body>
</html>
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip