Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.sql

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

Daylight Savings Time changes coming in 2007

I apologize ahead of time if this has been covered. I tried searching but found only the OS specific response to my question (http://www.microsoft.com/windows/timezone/dst2007.mspx).

With the coming changes to DST in 2007, is there -- or is there even a need to -- patch either SQL Server 2005 or 2000 to account for those changes?

Doug,

have you received any responses yet to this question? I too have the same question and have only found the page that you already linked too as well. Please let me know if you find out anything.

Thanks,

Chad (chad.smith@.lucasfilm.com)

|||

There has been a lot discussion in the SQL org about this. To date, we have determined SQL is pulling all dates from the OS so no updates from SQL are needed. If we eventually find a problem we'll definitely release an update.

Thanks,

Peter Saddow

|||What about releasing a "rebasing" utility like the one issued for Outlook and Exchange?|||How about date arithmetic? Wouldn't that be affected by this change?|||

Consider this:

You have a calculation that comes up with a date/time in the future with a result as UTC value. Let's assume that future date falls somewhere between 3/11, 2AM and 4/1 2AM. This UTC value must be wrong because you haven't applied any DST related patches yet. If you store this value for future use, it will be wrong.

You apply OS DST patch.

You do the same calculation as before. This time, UTC will not be different by just the amount of time that passed between two calculations. Extra hour will be added because that is what the patch corrects.

The first value is still wrong and something must be done about it. That's why Microsoft issued "rebasing" tool for Outlook and Exchange.

It's not enough to say: "you'll be fine as soon as you patch OS". This doesn't fix bad dates that are being calculated and stored today.

I'm still waiting for someone to explain to me why the above line of thinking doesn't apply to SQL Server (or any other Windows application).

All you MVPs out there?

|||The anwser to your question, is that SQL server itself is not responsible for the data being stored in it. So any application that is storing its data in SQL server will need to come out with an update to compensate for the change in Daylight Savings Time if the developer deems there to be an issue, where the dates and times that are stored in it would be affected.|||

By now SQL Server has been added to the list of Microsoft products affected by DST change. It will requre patching. That was the original issue that started this thread.

I don't know how SQL Server scheduler stores dates but some schedulers have been affected if they store dates as UTC (or GMT). For me, jury is still out on this.

|||

Here is some SQL script that I’ve come up with to calculate the daylight savings for -2006 and +2007.

http://2pttechnology.com/Web/forums/thread/19.aspx

|||I am a computer illiterazzi. I just want a simple, no hassle patch to fix my computer which is a Windows XP 2002 Service Pack. There are so many selections, I am terrified I will pick the wrong one and screw my expensive new computer. I don't use outlook to my knowledge. I can't find that the daylight savings patch has been downloaded automatically. There are a gadzillion downloads in my computer record. Please help. Thanks.

datetime problems with a searching query

hi;

i want to get some results for new my stored proce is below;

create Procedure HaberleriGetir

@.Kelime varchar(50),

@.tarih1 smalldatetime,

@.tarih2 smalldatetime,

@.KatID int,

@.lang char(5)

as

if @.lang = 'Hepsi'

select * from Haberler where HKatID = @.KatID and Metin like %@.Kelime% or Baslik like %@.Kelime% and Tarih between @.tarih1 and @.tarih2

else

select * from Haberler where HKatID = @.KatID and lang = @.lang and Metin like %@.Kelime% or Baslik like %@.Kelime% and Tarih between @.tarih1 and @.tarih2

incorrect near @.Kelime....

and i can't get a result between two dates like @.tarih1 & @.tarih2

Where are your quotes?

It should read

Metin like '%@.Kelime%'

|||You have to use Dynamic SQL like this: SET @.sqlstr= 'select * from Haberler where HKatID = ' + CONVERT(VARCHAR, @.KatID) + ' and Metin like ''%' + @.Kelime + '%'' or Baslik like ''%' + @.Kelime '%'' and Tarih between ''' + CONVERT(VARCHAR, @.tarih1) + ' and ' + CONVERT(VARCHAR, @.tarih2)........ then execute the sql statement by using the following SQL statement EXEC(@.sqlstr).... (NOTE: Declare the variable @.sqlstr before as VARCHAR(8000))

Date/Time Question

Hello,
I do alot of searching by date alone and i currently have a column that
will be converted to a date/time field. Is there any advantage to separatin
g
the date and time into 2 columns or would this be a waste of resources?
Thanks for the input.
benForgot to add the following:
how would grouping by work? i would need to group by the date part not the
time part.
thanks again for any help!
"Ben" wrote:

> Hello,
> I do alot of searching by date alone and i currently have a column that
> will be converted to a date/time field. Is there any advantage to separat
ing
> the date and time into 2 columns or would this be a waste of resources?
> Thanks for the input.
> ben|||SQL Server does not currently have seperate date and time datatypes, so you
really have no choice in the matter...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:BA013859-A89F-4DA1-8253-129DEA5FEE16@.microsoft.com...
> Hello,
> I do alot of searching by date alone and i currently have a column that
> will be converted to a date/time field. Is there any advantage to
> separating
> the date and time into 2 columns or would this be a waste of resources?
> Thanks for the input.
> ben|||SELECT DATEADD(dd, DATEDIFF(dd, 0, YourDateColumn), 0) AS TheDate, <other
columns>
FROM YourTable
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, YourDateColumn), 0) AS TheDate, <other
columns>
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0080BDC9-49FD-4BD3-872E-F03974B4D91C@.microsoft.com...
> Forgot to add the following:
>
> how would grouping by work? i would need to group by the date part not
> the
> time part.
> thanks again for any help!
> "Ben" wrote:
>|||> will be converted to a date/time field. Is there any advantage to
> separating
> the date and time into 2 columns
NO! SQL Server does not allow it anyway. If you place just the date into a
column, the time is midnight. If you place just a time, then the date is
either 1900-01-01 or 1899-12-31 depending on which tool you use.
(1899-12-30 is also a possibility, I believe.)
Instead, store the date and time in a single column. With an index on that
column, your best bet is a range query, even if you are only searching for
one day, for example:
SELECT columns FROM table
WHERE dt_column >= '20051109'
AND dt_column < '20051110'
Some further reading:
http://www.aspfaq.com/2206
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp|||Thank you for your replies. Seems a little complex the query but if there i
s
not other method i have no choice.
Thank you again.
"Adam Machanic" wrote:

> SQL Server does not currently have seperate date and time datatypes, so yo
u
> really have no choice in the matter...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:BA013859-A89F-4DA1-8253-129DEA5FEE16@.microsoft.com...
>
>|||You can use the convert() function to perform comparisons on only the date
portion of a datetime or smalldatetime column:
select convert(char(10),getdate(),112)
20051121
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:0080BDC9-49FD-4BD3-872E-F03974B4D91C@.microsoft.com...
> Forgot to add the following:
>
> how would grouping by work? i would need to group by the date part not
> the
> time part.
> thanks again for any help!
> "Ben" wrote:
>|||Thanks for the answer. That is what i was thinking i would have to do.
Another question: How do i group on just the date? do i use what was
mentioned by the other poster? I guess i would have to.
thanks again.
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO! SQL Server does not allow it anyway. If you place just the date into
a
> column, the time is midnight. If you place just a time, then the date is
> either 1900-01-01 or 1899-12-31 depending on which tool you use.
> (1899-12-30 is also a possibility, I believe.)
> Instead, store the date and time in a single column. With an index on tha
t
> column, your best bet is a range query, even if you are only searching for
> one day, for example:
> SELECT columns FROM table
> WHERE dt_column >= '20051109'
> AND dt_column < '20051110'
> Some further reading:
> http://www.aspfaq.com/2206
> http://www.aspfaq.com/2023
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>