SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Calculation Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

105ben
Starting Member

United Kingdom
16 Posts

Posted - 01/03/2013 :  09:25:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/03/2013 :  09:29:43  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

United Kingdom
16 Posts

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

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/03/2013 :  09:40:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 01/03/2013 09:41:56
Go to Top of Page

105ben
Starting Member

United Kingdom
16 Posts

Posted - 01/03/2013 :  09:41:54  Show Profile  Reply with Quote
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

United Kingdom
16 Posts

Posted - 01/03/2013 :  09:44:38  Show Profile  Reply with Quote
Ignore that then, yes it's MySQL
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 01/07/2013 :  08:25:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 01/07/2013 08:27:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000