Unanswered: Strange sp behavior when it runs with its name
I have a very strange problem. The behavior of SQL server 2000 is very strange.
I have a sp that when it runs with its name brings different (wrong) results than when running the sql code itself!!!
The sp creates and fills a table through a select into sql command. A field of the table is a date field. This field is filled up with a date from the select command to another table. Because this date field (CREATED) is UTC (universal time) we need to store it in the new table (tbl) as local date/time. For this reason we found an sql function (GetLocalDateTime) that do this very well (also includes the change in DST-daylight saving). Thus we have "select GetLocalDateTime(CREATED) into tbl".
The strange behavior is that when this sql code runs itself we get the right results for all date/time records but when it runs through a sp then we have all the records with the right local time except the records with date 30/10/2011 only. These records are one hour ahead!!!!!!! For all days before and after this day the results are ok!!!
This date is the date that we changed our clocks one hour back!