Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Last Year to Date report parameter Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 15, 2010 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:55 PM
Points: 6,582, Visits: 8,860
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
Posted Wednesday, September 15, 2010 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:14 PM
Points: 138, Visits: 924
You should use
select dateadd(yy,-1,dateadd(d,-1,getdate()))

Post #986412
Posted Wednesday, September 15, 2010 6:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #986817
Posted Wednesday, September 15, 2010 6:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 36,759, Visits: 31,214
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #986819
Posted Thursday, September 16, 2010 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, September 16, 2010 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:14 PM
Points: 138, Visits: 924
If there is a report parameter. The fomular will be
=DateAdd("yyyy",-1,DateAdd("d",-1,Today()))
Post #987329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse