Daylight saving time SQL

As you know the daylight saving time will change in 2007
http://www.microsoft.com/windows/timezone/dst2007.mspx
Microsoft realease patches for windows. But i cannot find any information
about if Microsoft SQL 2000 & 2005 are affected by this change.
Thank you
SQL Server gets its date and time from the operating system. Effectively,
it doesn't need to know anything about daylight savings time.
"Destin" <Destin@.discussions.microsoft.com> wrote in message
news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
> As you know the daylight saving time will change in 2007
> http://www.microsoft.com/windows/timezone/dst2007.mspx
> Microsoft realease patches for windows. But i cannot find any information
> about if Microsoft SQL 2000 & 2005 are affected by this change.
> Thank you
|||Ok, but i just want to be sure, because SharePoint is using the time of the
OS but SharePoint will need a patch (so maybe is the same thing for
SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
Do you know where i can find the official information from Microsoft.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server gets its date and time from the operating system. Effectively,
> it doesn't need to know anything about daylight savings time.
>
>
> "Destin" <Destin@.discussions.microsoft.com> wrote in message
> news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
>
>
|||> Ok, but i just want to be sure, because SharePoint is using the time of
> the
> OS but SharePoint will need a patch (so maybe is the same thing for
> SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
I believe there must be some data somewhere in sharepoint that needs to be
changed.
Anyway, the update is not released yet.
If there IS a forthcoming update to SQL Server (which I can't imagine) I
believe it will be released in much the same way, and you will hear about
it.

> Do you know where i can find the official information from Microsoft.
I don't think there will be any official information. I drive a Touareg and
I don't get notified when there is a recall on a Jetta. :-)
sql

Daylight saving time SQL

As you know the daylight saving time will change in 2007
http://www.microsoft.com/windows/timezone/dst2007.mspx
Microsoft realease patches for windows. But i cannot find any information
about if Microsoft SQL 2000 & 2005 are affected by this change.
Thank youSQL Server gets its date and time from the operating system. Effectively,
it doesn't need to know anything about daylight savings time.
"Destin" <Destin@.discussions.microsoft.com> wrote in message
news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
> As you know the daylight saving time will change in 2007
> http://www.microsoft.com/windows/timezone/dst2007.mspx
> Microsoft realease patches for windows. But i cannot find any information
> about if Microsoft SQL 2000 & 2005 are affected by this change.
> Thank you|||Ok, but i just want to be sure, because SharePoint is using the time of the
OS but SharePoint will need a patch (so maybe is the same thing for
SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
Do you know where i can find the official information from Microsoft.
Regards,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server gets its date and time from the operating system. Effectively,
> it doesn't need to know anything about daylight savings time.
>
>
> "Destin" <Destin@.discussions.microsoft.com> wrote in message
> news:3739B03C-E71F-4CA6-8DAF-5189A22A7162@.microsoft.com...
>
>|||> Ok, but i just want to be sure, because SharePoint is using the time of
> the
> OS but SharePoint will need a patch (so maybe is the same thing for
> SQL)(http://www.microsoft.com/windows/timezone/wss.mspx)
I believe there must be some data somewhere in sharepoint that needs to be
changed.
Anyway, the update is not released yet.
If there IS a forthcoming update to SQL Server (which I can't imagine) I
believe it will be released in much the same way, and you will hear about
it.

> Do you know where i can find the official information from Microsoft.
I don't think there will be any official information. I drive a Touareg and
I don't get notified when there is a recall on a Jetta. :-)

DateTime with TimeZone ?

Are there any plans to enhance the DateTime datatype to be able to store a timezone, and provide timezone aware arithmetic functions ?

The lack of timezone support seems a glaring omission - especially given that Microsoft's biggest DB competitor (Oracle) has a timestamp with timezone datatype. At present, you have to code all this yourself in SQL 2005. Is this not something that should be built into the DBMS ?

Thanks,

Andy Mackie

You can store it as UTC.

HTH, Jens Suessmeyer.

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.