How to not hardcode date range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to not hardcode date range?

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-07-31 : 14:22:13

Hello,

I have a simple query:

SELECT SUM(VENDTRANS.AMOUNTMST), DATAAREAID, ACCOUNTNUM
FROM VENDTRANS WHERE TRANSDATE >= '1/1/09' AND TRANSDATE <= '12/31/09' AND INVOICE = ''
GROUP BY DATAAREAID, ACCOUNTNUM

Problem is I do not want to hardcode the date range.

In year 2010 I want 1/1/2010 to 12/31/2010.
In year 2011 I want 1/1/2011 to 12/31/2011.

Do not want to go back to the query every year to adjust this.

Kind thanks in advance for any assistance on this.

Regards.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-31 : 14:34:10

Here's one way:


where  transdate >= dateadd(year, datediff(year, 0, getdate()), 0) 
and    transdate <= dateadd(year, datediff(year, 0, getdate()), '1900-12-31')

Be One with the Optimizer
TG

weipublic
Starting Member

19 Posts

Posted - 2009-07-31 : 14:35:45

Seems you want return something in current year, following should work.

DATEDIFWF(year, TRANSDATE, getdate())=0

If not sure which year, may need create a variable.

weipublic
Starting Member

19 Posts

Posted - 2009-07-31 : 14:38:27

sorry it should DATEDIFF(year, TRANSDATE, getdate())=0

and TG's query works too.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-07-31 : 14:40:01

Thank you so much!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources