Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nonlinearly
Starting Member
3 Posts |
 Posted - 2011-11-04 : 05:37:55
|
| Hi,I have a very strange problem. The behavior of SQL server 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!Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-04 : 07:41:58
|
| first check if there are multiple versions of function GetLocalDateTime available under different schema in your db. then check what credential it uses when run from sp and whats the default schema that credential is mapped on to.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nonlinearly
Starting Member
3 Posts |
Posted - 2011-11-04 : 08:36:51
|
| Thank you for your response.No there is not credential problem. But even if there was then why ONLY the 30/10/2011 records affected and not the next ones!!!! That is the mysterious. |
|
|
|
nonlinearly
Starting Member
3 Posts |
Posted - 2011-11-04 : 12:41:42
|
| Ok the problem is solved.Before the conversion function there was the command: SET DATEFIRST 1 that create the problem.Thanks anyway! |
|
|
|
|
|
|
Strange sp behavior when it runs with its name