SSRS 2005 Table Filter, getting last 3 years

Posted on 2009-04-20
Last Modified: 2012-05-06
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?
Question by:Wilkie2
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?

    Author Comment

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

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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


    Accepted Solution

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

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    How to increase the row limit in Jasper Server.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now