none
Issue comparing date field in report builder 3.0

    Question

  • List below is my expression:

    =IIf((Fields!TaskActualFinishDate.Value Is Nothing), " ", IIf(format(Fields!TaskActualFinishDate.Value, "MM/dd/yy") > format(Fields!TaskDeadline.Value, "MM/dd/yy"), 0, 1))

    But it seems like only the Month and Date fields are been compared not the hold date field MM/DD/YY. Can someone please tell me what I'm not doing correctly thank you so much. 

    Monday, September 09, 2013 9:57 PM

Answers

  • Hi

    First use IsNothing and not Is Nothing something like this

    =IIf(IsNothing(Fields!TaskActualFinishDate.Value), "", IIf(Format(Fields!TaskActualFinishDate.Value, "MM/dd/yy") > Format(Fields!TaskDeadline.Value, "MM/dd/yy"), 0, 1))

    now you verified the result with your database?


    Si se solucionó tu consulta no olvides marcar la respuesta de esta forma mantendremos el orden en el Foro.

    José Carlos Barba Gutierrez
    Lima - Perú
    Saludos

    • Marked as answer by Bharat64 Friday, September 13, 2013 2:08 AM
    Tuesday, September 10, 2013 8:32 PM
  • Hi Bharat64,

    According to your description, it seems that you want to compare two date in Reporting Services. After testing the expression you posted in my own environment, I also find that it only compare the Month and Date, doesn’t including the Year. Format function returns a string with formatting dates and numbers. When you compare the dates with format function, it is return to compare the value of the two strings. And it from left character to right, compare one by one. In your scenario, it will compare the Month first, and then Date, finally, it comes Year.

    So we can modify your expression to like below to achieve the goal:

    =IIf((Fields!TaskActualFinishDate.Value Is Nothing), "", IIf(format(Fields!TaskActualFinishDate.Value, "yy/MM/dd") > format(Fields!TaskDeadline.Value, "yy/MM/dd"), 0, 1))

    While we often use DateDiff function instead of format function to compare two dates, it also achieve the goal like below:

    =IIf(IsNothing(Fields!TaskActualFinishDate.Value), "", IIf(DateDiff("d", Fields!TaskDeadline.Value, Fields!TaskActualFinishDate.Value)>0, 0, 1))

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong 

    • Marked as answer by Bharat64 Friday, September 13, 2013 2:08 AM
    Wednesday, September 11, 2013 7:21 AM

All replies

  • Hi

    First use IsNothing and not Is Nothing something like this

    =IIf(IsNothing(Fields!TaskActualFinishDate.Value), "", IIf(Format(Fields!TaskActualFinishDate.Value, "MM/dd/yy") > Format(Fields!TaskDeadline.Value, "MM/dd/yy"), 0, 1))

    now you verified the result with your database?


    Si se solucionó tu consulta no olvides marcar la respuesta de esta forma mantendremos el orden en el Foro.

    José Carlos Barba Gutierrez
    Lima - Perú
    Saludos

    • Marked as answer by Bharat64 Friday, September 13, 2013 2:08 AM
    Tuesday, September 10, 2013 8:32 PM
  • Hi Bharat64,

    According to your description, it seems that you want to compare two date in Reporting Services. After testing the expression you posted in my own environment, I also find that it only compare the Month and Date, doesn’t including the Year. Format function returns a string with formatting dates and numbers. When you compare the dates with format function, it is return to compare the value of the two strings. And it from left character to right, compare one by one. In your scenario, it will compare the Month first, and then Date, finally, it comes Year.

    So we can modify your expression to like below to achieve the goal:

    =IIf((Fields!TaskActualFinishDate.Value Is Nothing), "", IIf(format(Fields!TaskActualFinishDate.Value, "yy/MM/dd") > format(Fields!TaskDeadline.Value, "yy/MM/dd"), 0, 1))

    While we often use DateDiff function instead of format function to compare two dates, it also achieve the goal like below:

    =IIf(IsNothing(Fields!TaskActualFinishDate.Value), "", IIf(DateDiff("d", Fields!TaskDeadline.Value, Fields!TaskActualFinishDate.Value)>0, 0, 1))

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong 

    • Marked as answer by Bharat64 Friday, September 13, 2013 2:08 AM
    Wednesday, September 11, 2013 7:21 AM