sql greater than 1 year - 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

General SQL Server Forums

New to SQL Server Programming

sql greater than 1 year

Author

Topic

angela_g
Starting Member

17 Posts

Posted - 2013-09-19 : 06:59:05

Hi im trying to create an SQL to find members who joined more then 12 months ago, can anyone help me with this please? This is what I have so far...
SELECT RE_EZPAY_AT_AGENCY_DAILY.SNAPSHOT_DATE,
RE_EZPAY_AT_AGENCY_DAILY.AGG_ORDER_NO,
RE_EZPAY_AT_AGENCY_DAILY.DEBT,
RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO_WCD,
RE_EZPAY_AT_AGENCY_DAILY.STATUS,
VE_MEMBER.MEMBER_JOIN_DATE
FROM RE_EZPAY_AT_AGENCY_DAILY
INNER JOIN VE_MEMBER
ON RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO = VE_MEMBER.MEMBER_NO
WHERE VE_MEMBER.MEMBER_JOIN_DATE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-19 : 07:04:56

WHERE ... < DATEADD(YEAR, -1, GETDATE())

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-19 : 07:06:40

WHERE VE_MEMBER.MEMBER_JOIN_DATE < dateadd(month,-12,getdate())

Too old to Rock'n'Roll too young to die.

angela_g
Starting Member

17 Posts

Posted - 2013-09-19 : 07:28:29

Thankyou both for your help...Im getting an error message... wondering if it could be something to do with the date format
SELECT RE_EZPAY_AT_AGENCY_DAILY.SNAPSHOT_DATE,
RE_EZPAY_AT_AGENCY_DAILY.AGG_ORDER_NO,
RE_EZPAY_AT_AGENCY_DAILY.DEBT,
RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO_WCD,
RE_EZPAY_AT_AGENCY_DAILY.STATUS,
VE_MEMBER.MEMBER_JOIN_DATE
FROM RE_EZPAY_AT_AGENCY_DAILY
INNER JOIN VE_MEMBER
ON RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO = VE_MEMBER.MEMBER_NO
WHERE VE_MEMBER.MEMBER_JOIN_DATE <DATEADD(YEAR, -1, GETDATE())

ORA-00904: "DATEADD": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 11 Column: 35

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-19 : 07:30:26

we are on MS SQL Server only here that's why our solutions will not work for you.

Better try to get help in an oracle forum