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
 General SQL Server Forums
 New to SQL Server Programming
 Date Calculation Query

Author  Topic 

105ben
Starting Member

16 Posts

Posted - 2013-01-03 : 09:25:05
Hello,

I need the following code to update payscale to 2 for every person has has been assigned to a staff role for over 5 years. I'm getting an #1064 error for the DATEADD part.

update person as p, staff_role_allocation as sra
set payscale = '2'
where p.person_id = sra.person_id and sra.start_date <= DATEADD(yy, -5, GETDATE())

can anybody see why?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-03 : 09:29:43
update person
set payscale = '2'
from person as p
join staff_role_allocation as sra
on p.person_id = sra.person_id
and sra.start_date <= DATEADD(yy, -5, GETDATE())


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

105ben
Starting Member

16 Posts

Posted - 2013-01-03 : 09:34:54
That gives me a #1064 again, this time on the From line.. Had tried this code previously too
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-03 : 09:40:35
Are you using sql server?
I suspect MySQL



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

105ben
Starting Member

16 Posts

Posted - 2013-01-03 : 09:41:54
not 100% sure to be honest :S Im using php MyAdmin, is that using SQL server (probably a stupid question I know)
Go to Top of Page

105ben
Starting Member

16 Posts

Posted - 2013-01-03 : 09:44:38
Ignore that then, yes it's MySQL
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-07 : 08:25:44
Try this

update person as p, staff_role_allocation as sra
set payscale = '2'
where p.person_id = sra.person_id and sra.start_date <= DATE_ADD(now(), interval -5 year)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -