Daylight Savings Time error SQL SERVER 2000

Hi all.

After DST change (US/Canada), when I run any of my reports, I get this error:

"An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name:
date "

Does anyone have or heard of a solution?

Thank you!

What is your date format, MM/DD/YYYY or DD/MM/YYYY?|||

Try installing SQL Server 2005 (and restore the databases on it) and run the reports.

Shyam

|||mm/dd/yyyy|||

I cannot give up using SQL Server 2000 just because of this issue

|||

I do not think you need to give up on SQL 2000 for this.

Can you share the script where you pass in the parameters?

|||<Value>=Globals!ReportName &amp;" between " &amp; Parameters!startdate.Value &amp; " &amp; " &amp; Parameters!enddate.Value</Value>
...
<ReportParameters>
<ReportParameter Name="startdate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(cstr(year(Now())) + "/" + "01" + "/" + "01" + " 00:00:01")</Value>
</Values>
</DefaultValue>
<Prompt>Start Date-Time (MM/DD/YYYY HH:MM:SS AM or PM)</Prompt>
</ReportParameter>
<ReportParameter Name="enddate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(cstr(year(Now())) + "/" + cstr(month(now())) + "/" + cstr(day(now())) + " 23:59:59")</Value>
</Values>
</DefaultValue>
<Prompt>End Date-Time (MM/DD/YYYY HH:MM:SS AM or PM)</Prompt>
</ReportParameter>
<ReportParameter Name="Team">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>%</Value>
</Values>
</DefaultValue>
<Prompt>Enter a Team with % as a wildcard character i.e. %Cust%</Prompt>
</ReportParameter>
</ReportParameters>|||

I do not see any problems in the script.

Please take a look at the server log and here's something for reference. If possible, please post what you can find from the log. HTH.

http://blogs.msdn.com/jgalla/archive/2006/12/05/diagnosing-rsinternalcatalogexception.aspx

|||

Problem was resolved after power outage ;) and server restart.

|||Reboot is always the "best" solution for all MS issues.

Daylight Saving Time DST 2007 Problems

I believe my machine is fully patched. The SQL Server believes that it is not yet DST, whereas the windows interface has switched. So what should I do here? (I'm in EST/EDT time zone -- New York City -- commands were run at 1:13 am.)

select current_timestamp,getutcdate()-getdate()

-- --

2007-03-13 00:13:24.843 1900-01-01 05:00:00.000

(1 row(s) affected)

SQL Server gets tim (using all three of these functions) from the Windows OS.

This is a quote from Books Online for getutcdate()-

Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

|||The windows gui gives me the right time. A cmd shell "time/t" command gives me the right time, but the T-SQL I quoted gets the wrong time and all my agent jobs are running an hour late, so something is up here...|||

so, instead of a 5 hour difference between the two you should see a 4 hour difference?

|||Yes. That's right.|||If you go to a cmd prompt and type "time" and open a query window and run "SELECT GETDATE()" Do you get the same hour?

When was the last time you rebooted the server?

It use to be, I don't know if it still works this way, Windows would fudge the Windows clock after DST and would reset the RTC clock when the computer rebooted.

So your RTC could say 1:13pm and Windows would report 2:13pm, until you rebooted.

|||We noticed that about 50% of the time the DLS Patch that we applied to Windows 2003 did not update the registry values for daylight and daylight end. We found that the time would update and display correctly on the OS, but anything that capture time via some time/date.dll would be an hour off.

Daylight Saving Time DST 2007 Problems

I believe my machine is fully patched. The SQL Server believes that it is not yet DST, whereas the windows interface has switched. So what should I do here? (I'm in EST/EDT time zone -- New York City -- commands were run at 1:13 am.)

select current_timestamp,getutcdate()-getdate()

-- --

2007-03-13 00:13:24.843 1900-01-01 05:00:00.000

(1 row(s) affected)

SQL Server gets tim (using all three of these functions) from the Windows OS.

This is a quote from Books Online for getutcdate()-

Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

|||The windows gui gives me the right time. A cmd shell "time/t" command gives me the right time, but the T-SQL I quoted gets the wrong time and all my agent jobs are running an hour late, so something is up here...|||

so, instead of a 5 hour difference between the two you should see a 4 hour difference?

|||Yes. That's right.|||If you go to a cmd prompt and type "time" and open a query window and run "SELECT GETDATE()" Do you get the same hour?

When was the last time you rebooted the server?

It use to be, I don't know if it still works this way, Windows would fudge the Windows clock after DST and would reset the RTC clock when the computer rebooted.

So your RTC could say 1:13pm and Windows would report 2:13pm, until you rebooted.|||We noticed that about 50% of the time the DLS Patch that we applied to Windows 2003 did not update the registry values for daylight and daylight end. We found that the time would update and display correctly on the OS, but anything that capture time via some time/date.dll would be an hour off.

daylight saving time (DST)

Hi,

How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007?

You don't need to. Sql Server gets its dates and times from the operating system.

|||

But I am facing some problems.......

Is there have any alternative for getdate() and dateDiff() function for this issue?

|||

Please excuse me. I wasn't thinking. Look at the following:How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007

|||

Yes, I have already gone through this link. But I am not cleared about the issue. I am using windows 2000 and 2003 OS.

Can Any body please tell me that what should be done from my end to make my sql server compatible to this DST Issue?

|||

Hi,

Actually, you don't need to anything. Just set the time zone of your operating system to the correct zone. If in this time zone, there is Daylight Saving Time, it will show up in the window.

You will need to apply the patches from that KB to have the correct start date of DST.