Post reply

Date question off IIF

  • February 29, 2016 at 12:50 am

    #325196

    Hello, I was wondering if anyone had any advice.

    In SSRS I have a simple expression:

    IIF(fields!date1.value >= fields!date2.value, "yes", "no")

    Is there a way I can get the query to compare the date value only - e.g. 2016/02/29 - and completely disregard hours / minutes / seconds? I'm finding the hours, minutes and seconds will affect the output if the two date values are the same.

    Advice gratefully received. I can amedin SSRS or in Management Studio.

  • February 29, 2016 at 2:14 am

    #1861736

    Can you not use Datepart?

  • February 29, 2016 at 2:18 am

    #1861738

    Or this:

    CONVERT(VARCHAR(10),GETDATE(),101)

  • February 29, 2016 at 2:28 am

    #1861740

    Thanks for getting back. I did try CONVERT but had the same problem as above.

    However, I've nearly solved the problem by using:

    IIF(DateDiff(DateInterval.Day(fields!date1.value, fields!date2.value)>=0, "no", "yes"

    However if the date 2 field has no value, it returns a value ("yes") - do you have any idea how I can amend the expression to show "no" if date 2 has no value?

    Thanks

  • February 29, 2016 at 2:38 am

    #1861742

    use datepart or something like

    =iif(DateDiff("d",fields!date1.value,fields!date2.value)>0,"yes","no")

    - Damian

  • February 29, 2016 at 2:48 am

    #1861744

    So something like this doesn't work? - IIF(CONVERT(VARCHAR(10),@date1,101)=CONVERT(VARCHAR(10),@date2,101), 'same day','different day')

    Test Script:

    Declare @date1 datetime

    Declare @date2 datetime

    set @date1 = getdate()

    set @date2 = Cast('02/29/2016' As DateTime)

    Select IIF(CONVERT(VARCHAR(10),@date1,101)=CONVERT(VARCHAR(10),@date2,101), 'same day','different day')

  • February 29, 2016 at 3:20 am

    #1861748

    Solved it via:

    IIF(IsNothing(fields!date2.value), "no",

    IIF(DateDiff(DateInterval.Day, fields!date1.value, fields!date2.value) >= 0, "no", "yes"))

  • February 29, 2016 at 6:19 am

    #1861772

    Also check out the Switch expression

    Switch(IsNothing(fields!date2.value), "no", DateDiff(DateInterval.Day, fields!date1.value, fields!date2.value) >= 0, "no", True, "yes")

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 8 posts - 1 through 7 (of 7 total)

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