none
Date filtering problem... any help is very much appreciated. RRS feed

  • Question

  • Hi Guys,

    I'm have a problem with filtering my reports based on date ranges.

    Basically, the database I have been provided is poorly written and the datatype in the databases are all varchar...even for DATES!!!.....

    anyway, I'm trying to filter on those dates which is causing me a huge headache!

    I have setup to calenders on the reporting page, one called fromdatecalender and the other todatecalaender.

    now if I pass the calenders selected values as strings to the reportviewer, I can filter on them as strings with

    =Fields!creationdate.Value  >=    =Parameters!fromdate.Value   AND
    =Fields!creationdate.Value  <=    =Parameters!todate.Value


    but this doesn't return the correct results as its a string comparison.

    I've tried all ways of casting the parameters before passing it...... and also just passing them as strings then using

    =CDate(Fields!creationdate.Value)  >=    =CDate(Parameters!fromdate.Value)   AND
    =CDate(Fields!creationdate.Value)  <=    =CDate(Parameters!todate.Value)

    I am configuring the filters on the report table  (Table Properties -> Filters).
    but this returns...

         "An error has occurred during report processing.
      • Failed to evaluate the FilterExpression."


    Can somebody please help me!  Thanks! Its very much appreciated.


    Saturday, May 10, 2008 11:31 AM

All replies

  • Have you tried BETWEEN date1 AND date2 ?

     

    Sunday, May 11, 2008 3:24 PM
  • Hi,

    If it's still relavent - just now, i encountered the same problem.
    It was because there where null values. it fails the filtering.

    Make sure you are not sending null values in a filtered column and you will be ok.

    Eyal
    Wednesday, September 3, 2008 3:18 AM
  • Hello,

    witch format do you save your dates in the DB? If its like this 'dd.mm.yyyy' you never get the correct results.

    You have to use 'convert' in your Query

    select convert(datetime,YourDateField ) as NewDateField from DatumTest order by convert(datetime,YourDateField)

    Then you can Filter as a normal DateField


    TestResults:

    Without convert, order by YourDateField
    01.01.2008
    01.01.2009
    01.03.2008
    25.03.2008

    ------------------

    Without convert, Filter =  where YourDateField between '01.01.2008' and '01.03.2008'
    01.01.2008
    01.01.2009
    01.03.2008

    ------------------

    With convert, order by YourDateField
    2008-01-01 00:00:00.000
    2008-03-01 00:00:00.000
    2008-03-25 00:00:00.000
    2009-01-01 00:00:00.000

    ------------------

    With convert, Filter =  where convert(datetime,YourDateField) between '01.01.2008' and '01.03.2008' order by convert (datetime,YourDateField)
    2008-01-01 00:00:00.000
    2008-03-01 00:00:00.000



    BanditTreiber


    Germany, NRW
    Wednesday, September 3, 2008 7:19 AM