????? ???? ?? ??? SQL Server 2005 ?? SQL Server 2000 ?????? ??? ??? ???? ?? ??? ???????? ???? ?? ??? 2007 ??? ???? ????

???? ?????? ???? ??????
???? ID: 931975 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

?????

????? 2005, ??? U. S. Congress ????? ???? ??????? ???? ??? ??? ?? act ?????? ??????? ?? ??????? ?????? ?????? ??? ??? (DST) ????? ??? ?? 2007 ??? ?????? ??? ?? act ???? ??, DST ???? ??? ?????? ??? ??????? ???? ?? ?? ?????? ??? ?? ??? traditionally ??????? ?? ?? ended ?????? ?? ?????? ????? ??? ??, DST ????? ??? 2: 00 A.M. ?? ????? ?????? ?? ??????? ???? ?? 2: 00 A.M. ?????? ??? ????? ?????? ?? ?? ?????? ?? ??????

????? ?????? 2007 ??? ???????? ???? ?? ??? ?????? ??? ??? summarizes ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
?? DST ???? ??????? ???????? DST 2007 ??? ??????? ???????? DST ???? ?????? ???????? DST 2007 ??? ?????? ??????
?????? ??? ????? ??????????? ??? ????? ????????????? ??? ????? ???????????? ??? ????? ??????
???? ?? ?????? 1, 2007????? 11, 2007???? ?? 28 ???????, 20074 ??????, 2007
?? ???? ????? ???? ?? ?? ???? ????? ???? ?? ??? Microsoft SQL Server 2005 ?? Microsoft SQL Server 2000 DST ??? ???????? ???? ?? ??? 2007 ????

???? ???????

?? ???? ?????

??? ???? SQL Server DST ?? ????? ??????? ?? ??? ???????? ???? ??? ?? ?? ???? ???????? ?? ??????? ????, ?? ??? ??????? ?? ???????? ?? ???????? ???????? ???? ?? ??? DST 2007 ???, ?? ????? ???????? ???? ?????:
  • 924840 Microsoft ???????? ???? ??? ????? ???? ??? ?? ?? Windows ?? ??? ?????? ??????? ????????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
    924840Windows ?? ??? ?????? 2007 ??????? ??? ???? ?? ?? ??????? ??????? ?????? ??
  • ??? ???? ??? SQL Server ????? ???? ?? ???????? ?? ??????? ??, ?? 931815 Microsoft ???????? ???? ??? ?????? ?????? ???????????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
    931815SQL Server 2005 ????? ???? ?? ??? ?? SQL Server 2000 ????? ???? ?? ??? ?????? 2007 ??? ????
  • ?? ???? ?? ????????? ???? ?? SQL ????? ????? ??? ???? ?? ??? SQL ????? ?? ??? ??????? ?????? ?? ???? ???? ?? ??? ???? ??? ???????, ?? ???????? ?????? ?????? ???? ?????? ??? ??, ?? ?????? ???? ????? ???????? ?? ????????? ???????? SQL ????? ?? ???? ?? ???????? ?? ????????? ?? ???? ?? ????? ??????, Windows SharePoint ??????, Microsoft CRM ????? ????, ?? on ???? ???? Microsoft ???????? ?? ??? ???? ???? ????? ?? ???????? ?? ???? ????, ?? ??? ????? Microsoft ??? ???? ?? ????:
    HTTP://support.Microsoft.com/GP/cp_dst

SQL ????? ??? ????? ?? ??????????

SQL ????? 2005 ?? SQL Server 2000 ???, SQL Server ??????? ????? ??? ??????? ?? ????? ?? ??? ????? ?? ?? ????? ??????? ????? ???? ??:
  • HIGH-resolution ?????
  • Low-resolution ?????
High-resolution ????? ??? ????? ??????????? CPU ?? ???? ?? ??? ???-?????? ?????? (RDTSC) ?? ??????? ?? ?????? ??? Low-resolution ????? ??? ????? ??????????? ?? ?????? ??GetTickCountMicrosoft Windows API ??? ????????

??????? ?????-?????? ????????? ????? ?? ?????????? ?????? ????? ???? ??? ????? ?? ??? ?? ??????? ?? ?????? ????? ??????? ?? ??????? ?? ????? ??? ?? ??????? measures ?? ??? ??? ???, ?????? ??? ?? ?????? ?????????? ???????? ???? ????? DST ??? ???????? ?? ?????? 2007 ????

?????? ?? ???, ?? ?????-?????? ??? ?? ????? ?????-?????? ?????????? ??? ????? ????? ???? ???:
  • ?????? ??? ???? lazy ????, ????? ?????? ?? scheduler ??????
  • ???? ?? ghost ??????? ?? ????:-shrink ????????? ?????
  • ???? ?? locks ?? latches Time-???-?????? ??????
  • ??????? ??? ?? ?????????? ???? ?? SQL ????? ????? ????? ?? ?????? ???????
  • ???? ?? ?????? ???WAITFOR???????? ???
SQL ????? ??? ?? ????? ?? ?????? ???? ????? ????? ?? ??????????? ?? ??? ??? ??????? ??? ?? ??? ??????? Windows ???????? ?????? ?? ??????? ?? ??? ?????, ??? ??????? ?? ???? ?? ?? ???????? ?????? ?? ??? ??? ??? ???? ???

?????? ?? ???, ?? ????? ????? ????? ?? ??????????? ?? ?????? ????? ???? ???:
  • ???? ?? SQL ????? Profiler ?? SQL Profiler ????? ???????????? ???????? ?? ???,??????? ????????, ????????????? ??????? ?? ??? ?????
  • ??? SQL Server Errorlog, ????? ??? ????, ?? ?????? ???????? ???? ??????? ??? ?? ??????? ???? ?? ?? ??? ???????
  • ?????? ???? ??? ????GetDate?????? ??GetUtcDate?????? ?? ??? ???? ???.
?? ???????? ?? ????? ????.. SQL ????? Profiler ?? SQL Profiler ?? ????? ?? ???? SQL Server ????? ?????? ????? ?????? ????? 2007 DST ??? ?? ???? ??????? ???? ?? ?? ???? ?????? ????? ?? ????? 2007 DST ??? ????????? ???? ?? ??? ???? ?? ???? ??? ?? ???????? ???, ??? ??????? ??? ?? ?? DST ?????????? ?? ???????? ???? ???
????? ?? ????? ?????? ????? ??:
EventSequence  EventClass         TextData              StartTime                EndTime                  Duration
156            Sql:StmtStarting   Select * From Table1  2007-03-11 01:59:57.187
157            Sql:StmtCompleted  Select * From Table1  2007-03-11 01:59:57.187  2007-03-11 03:00:07.187  9987
??? ???, ????? ?? ?????? 2007 DST ??? ???????? ?? ????? ??? ?????? ??????? ???? ?? ?? ???? ????? ?? ????? ??????:
EventSequence  EventClass         TextData              StartTime                EndTime                  Duration
178            Sql:StmtStarting   Select * From Table1  2007-11-04 01:59:54.967
179            Sql:StmtCompleted  Select * From Table1  2007-11-04 01:59:54.967  2007-11-04 01:00:05.030  10055

????? DST-??????? SQL Server issues 2007 ??? DST ??? ???????? ???? ?? ??? ??????? ???? ???

DateDiff ?? DateAdd ?????? ?? ??? ?? ?????? DST ??????? ???? ??

?? ?? ????? ???? Transact SQL ??? ?????? ?????? ?????? ?? ??? ?????? ?? ?????? ??? ?????? ???? ?? ???, ???? ?????? ????????????? ???? ???? ????? ??? ??, ??? ?? DST ??? ????????? ????? ??? ????? ??? ??? ???? ??DateDiff, ??DateAdd?????? ??????? DST ??????? ???? ???

?????? ?? ???, ??? ????????? ??? ???? ?? ???? ???? ?? ??? ????? ????? ????? ???? ??? ?????? ?? ?????? DST ??? ?? ?????? ??? ????? ??? ?? ??? DST ?????? 2007 ???, ?? ??????? 2007-03-11 ?? DST ?? ??????? ?????? ??? However, under the old DST system, 2007-04-01 would be the starting date of DST.
DECLARE @starttime datetime
DECLARE @endtime datetime
SELECT @starttime = GetDate() -- returns '2007-03-11 1:59:50.000'
WAITFOR DELAY '00:00:30'
SELECT @endtime = GetDate()   ?- returns '2007-03-11 3:00:20.000' 

If @starttime < '2007-04-01 3:00:00.000' And 
   @endtime > '2007-04-01 1:59:59.000'
	SELECT (cast((DATEDIFF(s, @starttime, @endtime)) as int) - 3600) AS TimeDiffInSecs
Else
	SELECT cast((DATEDIFF(s, @starttime, @endtime)) as int) AS TimeDiffInSecs

Go
When you run the statements, you receive the following result:
TimeDiffInSecs 
-------------- 
3,630
???????DateDiffsystem function is not DST aware, the statements return 3,630 seconds instead of 30 seconds.

To correct the time calculation in such scenarios, use theGetUtcDatefunction instead of theGetDate?????? ?? ??? ???? ???.. TheGetUtcDatefunction returns the current UTC time. The current UTC time is derived from the current local time together with the time zone setting in the operating system of the computer on which SQL Server is running.

The following are modified statements that work correctly:
/*-------------------------------------------------------
	  GetDate()		  GetUtcDate()
datetime  2007-03-11 1:59:50.000  2007-03-11 09:59:50.000
datetime  2007-03-11 3:00:20.000  2007-03-11 10:00:20.000
-------------------------------------------------------*/
DECLARE @starttime datetime
DECLARE @endtime datetime
SELECT @starttime = GetUtcDate() -- returns '2007-03-11 9:59:50.000'
WAITFOR DELAY '00:00:30'
SELECT @endtime = GetUtcDate()   ?- returns '2007-03-11 10:00:20.000'  
SELECT DATEDIFF (s, @starttime, @endtime) AS TimeDiffInSecs
Go
?? ?? ?????, ?? ???? ??????? ??? ?????? ??????????? ??:
TimeDiffInSecs 
-------------- 
30

SQL ????? ????? ?? ??????? ??? ?? ????? ?? DST ??????? ?????? ?? ??????

?? ???????? ?? ????? ????.. ???? ??? ??? ??????? ??? ?? SQL Server ????? ????? ?? ?? ??????? ??????? ??? ??????? ???? ??? ????? ?? ???????? 15 ???? ????? ??? ?????? 2007 ??? DST ???????? ???? ??, ?? SQL Server ????? ???????? ??? ?? ????? DST ????????? ????? SQL ????? ????? ???????? ?????? ?? ???? ???????? bases ?? ??? ?? ????? ?? ???? ??????? ??? ?? ?? ?????? ???? ???

????? ?? ????? ?????? ????? ??:
Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-03-11 01:30:00
CurrentTime    2007-03-11 01:30:00.343

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-03-11 01:45:00
CurrentTime    2007-03-11 01:45:00.343

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-03-11 03:00:00
CurrentTime    2007-03-11 03:00:00.357

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-03-11 03:15:00
CurrentTime    2007-03-11 03:15:00.357
?? ?????? ??? ?? ?? ???? ?? ???? ???? 2007-03-11 ?? ????? ?? ?? ?? ??? 02: 00: 00 ?? ????? ?? ?? ??? 2007-03-11 03: 00: 00 ???? ?????????

???????, ?? ?????? SQL Server ????? ?? ??????? ??? ?? ????? ??? ???? ???? ?? 2007 ?????? DST ???????? ???? ?? ???? ?? ????? ?? ???? ?? ??? ??? ????? ??????? ???? ?? ????????? 2: 00 A.M. ?? 1: 00 A.M. ?????? 4, 2007, ?? ???? ?? ??? SQL Server ????? ????? ???? ???? ???? ?????? ?? 2: 00 A.M. ???? ??????? ???? ?? ??? ????? ?? ????????? ????? ?? ?? ????? ?????? ??? ?????-2007 DST conventions ?? ??????? ?? ?? ?????? ??????? ???? ?? ?????? ???? ?????? ?? ???????? ???? ?? ??? DST 2007 ????

????? ?? ????? ?????? ????? ??:
Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-11-04 01:30:00
CurrentTime    2007-11-04 01:30:00.343

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-11-04 01:45:00
CurrentTime    2007-11-04  01:45:00.343

one hour plus 15 minutes gap here */

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-11-04 02:00:00
CurrentTime    2007-11-04 02:00:00.357

Job 'Daylight Savings Job 1' : Step 1, 'step 1' : Began Executing 2007-11-04 02:15:00
CurrentTime    2007-11-04 02:15:00.357
????? ?? ?? ????? ?? ????? ??????, ??? ?? 2007-11-04 01:45:00 ?? ????? ?? ?? ?? 2007-11-04 ?? ????? ?? ?? ?? ??? ?? ??-????-??-15-???? ?? ???? 02: 00: 00? ?? ??????? ????????? ????? ?????, ????? ?????, ??? ?????? ?????, ?? SQL ????? ??? ???? ??????? ??? ?? ????? ?? ???????? ?? ????

???

???? ID: 931975 - ????? ???????: 06 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
??????: 
kbhowto kbexpertiseadvanced kbsql2005engine kbinfo kbmt KB931975 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:931975

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com