Update statement using datediff error

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 2005 Forums

Transact-SQL (2005)

Update statement using datediff error

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-22 : 15:11:43

I have the following update SQLStatement String:

SET @SQLStatement =

'UPDATE TAB_ccsNetActions
SET emailflag = 0
WHERE datediff("d",a.DueDate,getdate()) <= @ContractorDueIn and ModuleName = '''+@ModuleName +''' and MODULERECORDID =' + convert(nvarchar(50), @ModuleID)

EXEC (@SQLStatement)

I am getting an error, where datediff("d",

Incorrect syntax......

Thank you very much for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-22 : 15:15:45

Why are you using dynamic SQL for this when it isn't necessary and is complicating your query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-22 : 15:19:16

UPDATE TAB_ccsNetActions
SET emailflag = 0
WHERE datediff(day,a.DueDate,getdate()) <= @ContractorDueIn and ModuleName = @ModuleName and MODULERECORDID =@ModuleID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:30:08

If you want to use an existing index on duedate use this

UPDATE TAB_ccsNetActions
SET emailflag = 0
WHERE a.DueDate> datedd(day,datediff(day,0 ,getdate())+(-1)* @ContractorDueIn,0) and ModuleName = @ModuleName and MODULERECORDID =@ModuleID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources