Last Year to Date report parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Last Year to Date report parameter

Last Year to Date report parameter

Rate Topic

Display Mode

Topic Options

Author

Message

gorstl

Posted Wednesday, September 15, 2010 6:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 3:24 PM
Points: 2, Visits: 25

Hello,
I am trying to build a report that needs to query records between Jan01 of last year through yesterdays date, last year. In other words, for today (9/15/2010) I would need 1/1/2009-9/14/2010. I am using the following for my EndDate
=DateAdd("y", -1, (Dateadd("d", -1, Today)))

It keeps pulling records from both years.
Any help for this Newbie would be appreciated.

Post #986221

WayneS

Posted Wednesday, September 15, 2010 8:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,370, Visits: 8,235

1. Change "Today" to "GetDate()".
2. In your example, you want the date range to end on 9/14/2010. I assume this is a typo, and should be 9/14/2009?

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #986340

Dung Dinh

Posted Wednesday, September 15, 2010 9:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:54 AM
Points: 113, Visits: 890

You should use

select dateadd(yy,-1,dateadd(d,-1,getdate()))

Post #986412

Jeff Moden

Posted Wednesday, September 15, 2010 6:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811

Dung Dinh (9/15/2010)

You should use

select dateadd(yy,-1,dateadd(d,-1,getdate()))

Agreed... now all you need to do is to provide the other needed date. Wink

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #986817

Jeff Moden

Posted Wednesday, September 15, 2010 6:20 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 32,923, Visits: 26,811

WayneS (9/15/2010)

1. Change "Today" to "GetDate()".
2. In your example, you want the date range to end on 9/14/2010. I assume this is a typo, and should be 9/14/2009?

I've got the sneaky feeling that this is going to be for an SSRS report which would explain "Today" I think...

Anyway, I agree... the op needs to clarify what the date range should actually be.

--Jeff Moden

Post #986819

gorstl

Posted Thursday, September 16, 2010 5:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 3:24 PM
Points: 2, Visits: 25

My apologies and thanks for the help. It is for SSRS. The date range should have been described as January 1st of last year (1/1/2009) through yesterdays month/day/lastyear. This would be for a report parameter EndDate.

edited for mistake

Post #987128

Dung Dinh

Posted Thursday, September 16, 2010 9:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:54 AM
Points: 113, Visits: 890

If there is a report parameter. The fomular will be
=DateAdd("yyyy",-1,DateAdd("d",-1,Today()))

Post #987329

« Prev Topic | Next Topic »

Permissions