Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SSRS 2005 Table Filter, getting last 3 years

I have a table in a report that I need to only print if the field date is within the last 3 years.  I have tried several things and I either get all dates or none.
I have tried the following:
Table Properties, Filters
See attachments of screenshots.

What am I doing wrong?
Table-Filter.doc
0
Wilkie2
Asked:
Wilkie2
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the field datetime?
if yes, you shall not need the CDate()
also, don't use the ToShortDateString() function, because if it returns the date in format MM/DD/YYYY (or certain other formats that are not YMD), the >= cannot work, as it will be compared on the string bases, not on the date bases.

finally, "now -3 years": should that be "now - 3 years" (=> 20/04/2006 .. today ), or this and the previous 2 full years ( => 1/1/2007 .. today ), or what?
0
 
Wilkie2Author Commented:
The field is datetime.
I need:   now - 3 years" (=> 20/04/2006 ..

So if I have my field date as 20/04/2005 I do not want to list it, but if my date field is 20/04/2006 I do want it listed.  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would be, with pure sql server: not sure if that applies to srs:
your_date_field > dateadd(year, -3, convert(datetime, convert(varchar(10), getdate(), 120), 120) )

Open in new window

0
 
Wilkie2Author Commented:
That is the problem, cannot use pure sql server.  Expressions in SSRS is a little different.  
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now