Finding Records between two years

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

Finding Records between two years

Author

Topic

amphillips
Starting Member

3 Posts

Posted - 2013-06-19 : 14:05:49

I'm trying to write a query that will be used several times (potentially to create a view) that will find records that are dated between the current year and two years ago. Example: if I were to run this now, it should pull up all records dated from 2011 to 2013.

I'm not sure where to even start trying to capture just the year. Any suggestions?

select afe_number, rpt_cntr, account, aquisition, SUM(line_amount), gl_date, gl_line_description
from dmrt_nfx.xxnf_gl_detail_table
where rc_uid in (288070, 288071, 288072)
and datepart(year, gl_date) between dateadd(year,-1,GETDATE()) and datepart(year, getdate())
group by afe_number, rpt_cntr, account, aquisition, gl_date, gl_line_description

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 14:09:24

Aren't you already doing that via the "and datepart..." in your where clause? It might be more efficient to do that like shwon below:

SELECT  afe_number ,
        rpt_cntr ,
        account ,
        aquisition ,
        SUM(line_amount) ,
        gl_date ,
        gl_line_description
FROM    dmrt_nfx.xxnf_gl_detail_table
WHERE   rc_uid IN ( 288070, 288071, 288072 )
		AND gl_date >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-2,0)
		AND gl_date < DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
GROUP BY afe_number ,
        rpt_cntr ,
        account ,
        aquisition ,
        gl_date ,
        gl_line_description

amphillips
Starting Member

3 Posts

Posted - 2013-06-19 : 14:14:14

Thanks for the tip and help!
When I try running the adjustment, it gives an error.

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-19 : 14:16:16

Ah, you are using Oracle. This forum is for Microsoft SQL Server, and the query I posted is T-SQL syntax. I am not familiar with Oracle - you might get better and faster responses at an oracle forum or a generalized database forum.

amphillips
Starting Member

3 Posts

Posted - 2013-06-19 : 14:18:21

oops! Thanks anyways!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources