Strange sql error - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Strange sql error

Author

Topic

mcp111
Starting Member

44 Posts

Posted - 2004-05-07 : 10:35:25

I get this error on the following line for one of my stored procedures.

select @start_date=dateadd("d",-1,getdate())

Invalid parameter 1 specified for dateadd

The strange thing is that another stored procedure with the exact same line does not give any error.

Can you explain this anomaly?

I have tried rebooting the m/c several times but that particular stored procedure always gives this error.

The syntax is correct so I am really puzzled!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 10:41:05

select @start_date=dateadd(dd,-1,getdate())

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mcp111
Starting Member

44 Posts

Posted - 2004-05-07 : 10:57:22

Thanks, that worked.

But why does dateadd("d",-1,getdate()) work in one procedure and not in another?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-07 : 11:19:47

The only thing i can think of....is DB compatability level....are the databases at different sql versions?...sql6.5 or sql7 or sql2k?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-07 : 11:27:15

set quoted_identifier off/on

With it on it is taking the "" as a delimitter for the d which is valid.
Becomes the equivalent of

select dateadd([d],-1,getdate())

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources