Last Year to Date report parameter

Question

Post reply

Last Year to Date report parameter

gorstl

Old Hand

Points: 304
More actions
September 15, 2010 at 6:49 am

#224065

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Dung Dinh SSCrazy Points: 2513 More actions · Answer 2

You should use

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

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. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gorstl Old Hand Points: 304 More actions · Answer 5

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

Dung Dinh SSCrazy Points: 2513 More actions · Answer 6

If there is a report parameter. The fomular will be

=DateAdd("yyyy",-1,DateAdd("d",-1,Today()))