Daylight Savings Patch for 2000 Server

I was wondering if there is a simple patch for 2000 server. It seems that the only fix is to edit the registry and to use the time zone editor app. If there is a simple patch like there is for the 2003 servers i would appreciate it if anyone can show me where. I have about 80+ servers with 2000!!!

Much appreciated!

Look at this KBA http://support.microsoft.com/?kbid=928388 for more information.|||

This is the actual kba for windows 2000 server: http://support.microsoft.com/kb/914387

As you can see, there's really no quick patch to run.

|||I don't understand. SQL Server has absolutely no clue what the time is, time zone, or anything else. It gets all of its timing information from Windows. So, if you fix the time in Windows, you are done. You aren't finding a patch, simply because there isn't one since there isn't anything in SQL Server to fix.|||If you are based in the USA then as per the KBA you need to patch as per the recent time zone settings.|||Which is precisely the point. You patch WINDOWS. You do NOT patch SQL Server, because there is no SQL Server patch for this.|||Michael, the original poster was trying to patch windows .... Windows Server 2000, not SQL 2000. Yes, not all of us are using Server 2003 on all our hardware.

So, I guess is it a manual patch for my Server 2000 machines? Any body found a better way?

Thanks,

Vidal|||

Hi Guys

I think I found a better way!!!

Tech Republic had an article (HTTP://articles.techrepublic.com.com/5100-10877-61680.htlm) that has a tool called TZEdit where you set the new times zone setting without having to hack the registry.

I installed it last night and it works , I'm deploying it to the rest of my Windows 2000 Servers today, just read the "help file" and make sure you go to control panel and unselect and reselect your time zone to have your changes take effect

Hope that helps ,Mike

Daylight Savings Patch for 2000 Server

I was wondering if there is a simple patch for 2000 server. It seems that the only fix is to edit the registry and to use the time zone editor app. If there is a simple patch like there is for the 2003 servers i would appreciate it if anyone can show me where. I have about 80+ servers with 2000!!!

Much appreciated!

Look at this KBA http://support.microsoft.com/?kbid=928388 for more information.|||

This is the actual kba for windows 2000 server: http://support.microsoft.com/kb/914387

As you can see, there's really no quick patch to run.

|||I don't understand. SQL Server has absolutely no clue what the time is, time zone, or anything else. It gets all of its timing information from Windows. So, if you fix the time in Windows, you are done. You aren't finding a patch, simply because there isn't one since there isn't anything in SQL Server to fix.|||If you are based in the USA then as per the KBA you need to patch as per the recent time zone settings.|||Which is precisely the point. You patch WINDOWS. You do NOT patch SQL Server, because there is no SQL Server patch for this.|||Michael, the original poster was trying to patch windows .... Windows Server 2000, not SQL 2000. Yes, not all of us are using Server 2003 on all our hardware.

So, I guess is it a manual patch for my Server 2000 machines? Any body found a better way?

Thanks,

Vidal|||

Hi Guys

I think I found a better way!!!

Tech Republic had an article (HTTP://articles.techrepublic.com.com/5100-10877-61680.htlm) that has a tool called TZEdit where you set the new times zone setting without having to hack the registry.

I installed it last night and it works , I'm deploying it to the rest of my Windows 2000 Servers today, just read the "help file" and make sure you go to control panel and unselect and reselect your time zone to have your changes take effect

Hope that helps ,Mike

datetime diff query syntax

Hi.
I'm trying but not getting correct results.

I have two tables
one with app, msg, time
(varchar,datetime,varchar)

app1 start 2006-04-03 13:33:36.000
app1 stuff 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000
app1 start 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 stuff 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app3 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app2 start 2006-04-03 13:33:36.000
app2 end 2006-04-03 13:33:36.000
app3 end 2006-04-03 13:33:36.000
app1 end 2006-04-03 13:33:36.000

and another with dr watson crash info
(varchar, datetime)
app1 2006-04-03 13:33:36.000
app2 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app1 2006-04-03 13:33:36.000
app3 2006-04-03 13:33:36.000

I'm trying to make a query that will allow
me to see what entries in the first table
occurred wtihin, say, a minute, or maybe 40
seconds of any of the entries in the second
table.

I want all the entries in the second table to
be present, so I know it has to be some sort
of join, probably an outer join.

my syntax is giving me bad results, probably
because I'm just out of practice.

can someone tell me how to put a query together
so I see the data I'm looking for?
Thanks
Jeff

Jeff KishJeff Kish wrote:
> Hi.
> I'm trying but not getting correct results.
(snip)

There are a couple of different ways to do this. This one may not be
the best. It's just the first thing that popped into my mind. Hope it
helps. Your sample data was all the same timestamp. I created sample
data where a crash occurs within one minute of an entry for app1 and
another crash within a minute of an entry for app3. App2 is output in
the results becuase you specifically requested that.

Christopher Secord

create table AppMessage (
App char(4),
MsgType char(5),
MsgDate datetime
)
create table DRWatsonCrash (
App char(4),
CrashDate datetime
)

insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')

insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')

select AppMessage.App as Application, MsgType, MsgDate
from AppMessage, DrWatsonCrash
where AppMessage.App = DRWatsonCrash.App
and CrashDate between dateadd(minute,-1,MsgDate) and
dateadd(minute,1,MsgDate)
union all
select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
from DRWatsonCrash
order by Application, MsgDate|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have two tables
> one with app, msg, time
> (varchar,datetime,varchar)
> app1 start 2006-04-03 13:33:36.000
> app1 stuff 2006-04-03 13:33:36.000
> app1 end 2006-04-03 13:33:36.000
> and another with dr watson crash info
> (varchar, datetime)
> app1 2006-04-03 13:33:36.000
> app2 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app1 2006-04-03 13:33:36.000
> app3 2006-04-03 13:33:36.000
>
> I'm trying to make a query that will allow
> me to see what entries in the first table
> occurred wtihin, say, a minute, or maybe 40
> seconds of any of the entries in the second
> table.
> I want all the entries in the second table to
> be present, so I know it has to be some sort
> of join, probably an outer join.

There is a standard recommendation for this sort of posts, and that is
that you post:

o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it very easy to copy and paste into a query tool to develop a
tested solution.

With the information you have given, I can only give a non-tested solution,
which is also is just a guess of what you are looking for.

SELECT w.app1, w.datetimecol, o.event, o.datetimecol
FROM drwatson w
LEFT JOIN othertable o
ON w.app = o.app
AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 5 Apr 2006 21:42:37 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have two tables
<snip>
>There is a standard recommendation for this sort of posts, and that is
>that you post:
>o CREATE TABLE statments for your tables.
>o INSERT statements with sample data.
>o The desired output given the sample.
>This makes it very easy to copy and paste into a query tool to develop a
>tested solution.
I understand. I'll remember this in the future.
>With the information you have given, I can only give a non-tested solution,
>which is also is just a guess of what you are looking for.
> SELECT w.app1, w.datetimecol, o.event, o.datetimecol
> FROM drwatson w
> LEFT JOIN othertable o
> ON w.app = o.app
> AND abs(datediff(ss, w.datetimecol, o.datetime.col)) <= 40

and also the other message reply said...

>There are a couple of different ways to do this. This one may not be
>the best. It's just the first thing that popped into my mind. Hope it
>helps. Your sample data was all the same timestamp. I created sample
Yes, I was in a hurry and was careless. Normally the data is very
much just as you thought below.
>data where a crash occurs within one minute of an entry for app1 and
>another crash within a minute of an entry for app3. App2 is output in
>the results becuase you specifically requested that.
>Christopher Secord
>create table AppMessage (
>App char(4),
>MsgType char(5),
>MsgDate datetime
>)
>create table DRWatsonCrash (
>App char(4),
>CrashDate datetime
>)
>insert AppMessage values ('app1','start','2006-04-03 13:33:36.000')
>insert AppMessage values ('app1','stuff','2006-04-03 13:43:36.000')
>insert AppMessage values ('app1','end','2006-04-03 13:53:36.000')
>insert AppMessage values ('app2','start','2006-04-04 13:33:36.000')
>insert AppMessage values ('app2','stuff','2006-04-05 13:33:36.000')
>insert AppMessage values ('app2','end','2006-04-06 13:33:36.000')
>insert AppMessage values ('app3','start','2006-04-06 13:43:36.000')
>insert AppMessage values ('app3','end','2006-04-06 13:44:36.000')
>insert DRWatsonCrash values ('app1','2006-04-03 13:42:56.000')
>insert DRWatsonCrash values ('app2','2006-04-03 13:33:36.000')
>insert DRWatsonCrash values ('app3','2006-04-06 13:43:56.000')
>
>select AppMessage.App as Application, MsgType, MsgDate
>from AppMessage, DrWatsonCrash
>where AppMessage.App = DRWatsonCrash.App
>and CrashDate between dateadd(minute,-1,MsgDate) and
>dateadd(minute,1,MsgDate)
>union all
>select App as Application, 'DRWatsonCrash', CrashDate as MsgDate
>from DRWatsonCrash
>order by Application, MsgDate

Thanks much. I'll try both solutions.
I appreciate the feedback.
Jeff

datetime and timezone

Hi All,

I just wanted to confirm what I've found:

A datetime field is not stored independent of timezone?

In otherwords, I have a java app that accesses MSSQL via JDBC. In java, the 'Timestamp' object is internally represented as GMT, but when printed, it looks at the timezone of the OS to determine how to display it.

I thought that MSSQL would be the same in this respect but it seems not to be...

I set a datetime field to, for example, 10:30 am. I then changed my OS's (win xp) timezone from eastern to central, then opened up sql server management studio. The datetime field still showed as 10:30 am.

Does this sound right or am I doing something wrong?

Thanks,

Martin

No, you are right.

You could use the server function to represent the UTC time using the GetUTCDate() function.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||The stored/saved data should not be affected by the change in the system time zone. What's saved is saved. The display of the data is the job of the client app.|||

Thanks for the reply Oj & Jens,

Oj, in the example I gave, the client app is SQL Server Management studio. Wouldnt that handle a timezone change & display appropriately? Are you saying that an app other than SQL Server Management studio would, if properly written, display the time correctly? I also saw this behavior with sqlcmd. If a raw time is kept in the database, is it up to the client to somehow know what timezone the date was written as? In other words, I could write my client app to take the time I get from mssql & adjust it according to the current timezone, but how do I know what timezone the un-adjusted time is relative to? It seems to save the time however I write it, so if I write 6:00pm and I happen to be in EST, it gets saved in MSSQL as 6:00pm. How would the client app know that that time is relative to EST?

Also, Jens said that this is how it is. Are you guys contradicting each other or am I misunderstanding? Sorry if I'm unclear with any of this.

Thanks,

Martin

|||Well, it depends :-) This is a implementation detail, if you want to store the local time of the client you should use the client functionality to determine a datetime and write it to the database. if you store no information about the time zone, you will probably don′t know when the row was inserted compared to other rows, inderted in other time zones. if you don′t care about time zones, because you are only acting locally / regionally then you are fine. If you want to let the Server determine the time for you, you should use a server function rather than a client function, provoding a single source of date information.

-Jens.|||

Martin,

Workbench will just display the data as it is returned from the server. So, if the data was saved as 20010101, it will always be returned as 20010101. If it is returned as anything else, it would be a major data problem.

Getutcdate() function is not meant to convert your existing datetime data and returning it in UTC format. The function is meant for getting a datetime in UTC and store that into the database instead of getdate(). Please check book online for additional info.