subtract a year from current date for SP

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)

subtract a year from current date for SP

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-12 : 11:45:33

Hey all, how do you take the current date and subtract a year from it in a SP?

What I want to do is...

Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-12 : 12:01:58

will this do?
select dateadd(y, -1, getdate())

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-12 : 12:12:23

Yes it did. thank you.. i just had to change it to dateadd(yyyy, -1, getdate()) as Date1

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-12 : 12:18:23

Okay, how would I use that Date1 to compare in my HAVING clause though?

My SP so far is:


SELECT     SUM(dbo.PRUnionBene.rate) as HFRate, dbo.PREmployees.co, dbo.PREmployees.empl, (dbo.PREmployees.lname + ', ' + dbo.PREmployees.fname) as fullname, dbo.PREmployees.last_pay, 
                      dbo.PREmployees.union_cd, dbo.PRRate.craft, dbo.PRRate.rate1, dbo.PRRate.rate2, dbo.PRUnionBene.paytype, dbo.PRUnionDemog.locno, dateadd(yyyy, -1, getdate()) as Date1
FROM         dbo.PREmployees INNER JOIN
                      dbo.PRRate ON dbo.PREmployees.union_cd = dbo.PRRate.union_cd INNER JOIN
                      dbo.PRUnionBene ON dbo.PREmployees.union_cd = dbo.PRUnionBene.union_cd INNER JOIN
                      dbo.PRUnionDemog ON dbo.PREmployees.union_cd = dbo.PRUnionDemog.union_cd
GROUP BY dbo.PREmployees.co, dbo.PREmployees.empl, dbo.PREmployees.lname, dbo.PREmployees.fname, dbo.PREmployees.last_pay, 
                      dbo.PREmployees.union_cd, dbo.PRRate.craft, dbo.PRRate.rate1, dbo.PRRate.rate2, dbo.PRUnionBene.paytype, dbo.PRUnionDemog.locno
HAVING     (dbo.PREmployees.co = '2') AND (dbo.PRRate.craft = 'BR') AND (dbo.PRUnionBene.paytype = 1) AND (dbo.PREmployees.last_pay > Date1)
ORDER BY dbo.PREmployees.lname

When ran in QA i get "Invalid column name: Date1 "
What can I do here?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-12 : 12:19:58

HAVING ... AND (dbo.PREmployees.last_pay > dateadd(yyyy, -1, getdate()))

so if you don't need it in the select list you needn't put it there.

Go with the flow & have fun! Else fight the flow

hueby
Posting Yak Master

127 Posts

Posted - 2005-01-12 : 12:22:04

Ohhhhhhhhhhhhhhh! Thanks spirit1. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources