Post reply

Help with Dates

  • May 16, 2007 at 12:29 pm

    #116810

    I am a new to SQL.  I need some help with an IIF Statement and Dates.

    I am trying to get a report to show Amount in columns for different weeks, so I am trying to use an IIF statement to capture the dates for the week.  It comes up with different errors, but the most common one is this:

    The Value expression for the field ‘Week2’ contains an error: Operator '>' is not defined for type 'Date'

    Here is my IIF Statement:

    =IIf(Fields!TransDate.Value >= 04/29/07, "Fields!Amount.Value", "-") AND IIf(Fields!TransDate.Value <= 05/05/07, "Fields!Amount.Value", " ")

     

    If anybody can help I would appreciate it. 

     

    Thanks

     

  • May 17, 2007 at 8:45 am

    #707166

    For comparison of dates you need to use the DateDiff Function so your expression should be, I am also changing your AND to & as that is the concatenation operator in Reporting Services:

    =IIF(DateDiff(DateInterval.Day, "4/29/07", Fields!TransDate.Value)>=0, Fields!Amount.Value, "-") & IIf(DateDiff(DateInterval.Day, "05/05/07", Fields!TransDate.Value)<=0, Fields!Amount.Value, " ").

    DateDiff will return an integer which is the # of Days, in this example, between the dates.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

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