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 |
|
ramdas
Posting Yak Master
181 Posts |
 Posted - 2005-03-28 : 12:42:01
|
| Hi folks,I am running the following statement on SQL Server 2000 Database:DELETE from tableaWHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)AND effective_date < CONVERT(varchar(10),GETDATE(),120)tablea has effective_date defined as smalldatetime.When i run the sql, i get the following error:DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'The effective_date column is not a computed column. Any idea as to why the statement should genrate the error.Ramdas NarayananSQL Server DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-28 : 14:31:33
|
| Why are you doing a conversion? Since you aren't removing the time from GETDATE(), why not just this:DELETE from tableaWHERE effective_date >= GETDATE()-3AND effective_date < GETDATE()Indexed view perhaps?Tara |
 |
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-03-28 : 17:45:30
|
| Ramdas,Can you post some code to duplicate the issue? I can't duplicate that error on my end, no matter how ARITHABORT is set. I'm trying to figure out what else is going on with this...Tara: The CONVERTs posted do trucate the time portion; note that only 10 characters are being used... But personally, I would prefer to do it like:DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)---Adam MachanicSQL Server MVPhttp://www.datamanipulation.net |
|
|
|
ramdas
Posting Yak Master
181 Posts |
Posted - 2005-03-29 : 10:58:46
|
| Hi folks,I dropped and re-created the indexes on the table, the problem with delete went away. The effective_date column was part of an index which might have become corrupt.Thanks for the insight.RamdasRamdas NarayananSQL Server DBA |
|
|
|
|
|
|
|
|
DELETE Statement