none
Conversion from string "" to type 'Date' is not valid

    Question

  • I am making a report in SSRS that is looking at time information.  However, there are some cases where the information may be NULL.  I was trying to figure out a work around for the NULL, but I still get #error whenever I try it.  I thought this code may work since I know that the last field will always have a datetime associated with it.

    =IIF(Fields!HistorySCRStartTime.Value is Nothing, TimeValue(Fields!HistoryEngineStartTime.Value), TimeValue(Fields!HistorySCRStartTime.Value))

    I still get the conversion from string "" to type 'Date' is not valid error message.  What am I missing?

    Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1Rel
    Microsoft .NET Framework Version 4.0.30319 SP1Rel
    Installed Version: IDE Standard
    Microsoft Visual Basic 2010   01011-532-2002361-70634
    Microsoft Visual C# 2010   01011-532-2002361-70634
    Microsoft Visual Studio Tools for Applications 3.0   01011-532-2002361-70634

    Microsoft Visual Web Developer 2010   01011-532-2002361-70634
    SQL Server Analysis Services   
    Microsoft SQL Server Analysis Services Designer
    Version 11.0.3000.0

    SQL Server Integration Services   
    Microsoft SQL Server Integration Services Designer
    Version 11.0.2100.60

    SQL Server Reporting Services   
    Microsoft SQL Server Reporting Services Designers
    Version 11.0.3000.0

    Visual Studio 2010 Shell (Integrated) - ENU Service Pack 1 (KB983509)   KB983509
    This service pack is for Visual Studio 2010 Shell (Integrated) - ENU.

    • Edited by Tim.K.eq Saturday, November 09, 2013 8:45 PM
    Saturday, November 09, 2013 5:39 PM

Answers

  • Thank you Madhu, but it also gave me the same error.

    I have tried this now:

    =IIF(Fields!HistorySCRStartTime.Value="", TimeValue(NOW()), FORMATDATETIME(IIF(Fields!HistorySCRStartTime.Value="", NOTHING, Fields!HistorySCRStartTime.Value),DateFormat.LongTime))

    and it worked fine, but when I tried this for another expression:

    =IIF(Fields!HistorySCRStopTime.Value="", TimeValue(NOW()), FORMATDATETIME(IIF(Fields!HistorySCRStopTime.Value="", NOTHING, Fields!HistorySCRStopTime.Value),DateFormat.LongTime))


    it gave me the "" to type 'Date' is not valid on the second expression.

    What is odd is, it filled in the date instead of the #ERROR, but still gives me an error message.

    After I closed the project and opened it again, the runtime errors went away.

    • Edited by Tim.K.eq Saturday, November 09, 2013 11:16 PM Solved
    • Marked as answer by Tim.K.eq Saturday, November 09, 2013 11:16 PM
    Saturday, November 09, 2013 6:37 PM

All replies

  • Hi Tim,

    Can you give a try with below code and let me know if it will not work.

    =IIF(IsNothing(Fields!HistorySCRStartTime.Value)
        , TimeValue(Fields!HistoryEngineStartTime.Value)
        , IIF(IsNothing(Fields!HistorySCRStartTime.Value), Now(), TimeValue(Fields!HistorySCRStartTime.Value))

    Thanks, Madhu

    Saturday, November 09, 2013 6:03 PM
  • Thank you Madhu, but it also gave me the same error.

    I have tried this now:

    =IIF(Fields!HistorySCRStartTime.Value="", TimeValue(NOW()), FORMATDATETIME(IIF(Fields!HistorySCRStartTime.Value="", NOTHING, Fields!HistorySCRStartTime.Value),DateFormat.LongTime))

    and it worked fine, but when I tried this for another expression:

    =IIF(Fields!HistorySCRStopTime.Value="", TimeValue(NOW()), FORMATDATETIME(IIF(Fields!HistorySCRStopTime.Value="", NOTHING, Fields!HistorySCRStopTime.Value),DateFormat.LongTime))


    it gave me the "" to type 'Date' is not valid on the second expression.

    What is odd is, it filled in the date instead of the #ERROR, but still gives me an error message.

    After I closed the project and opened it again, the runtime errors went away.

    • Edited by Tim.K.eq Saturday, November 09, 2013 11:16 PM Solved
    • Marked as answer by Tim.K.eq Saturday, November 09, 2013 11:16 PM
    Saturday, November 09, 2013 6:37 PM
  • Hi Tim,

    If you have a look at the function TimeValue(), it will only return the Time part for the field and assigns 01/01/01 as date part:

    Returns a Date value containing the time information represented by a string, with the date information set to January 1 of the year 1.

    And can you please try the below code, hope it will work:

    =IIF(IsNothing(Fields!HistorySCRStopTime.Value) OR Fields!HistorySCRStopTime.Value=""

          , TimeValue(Now())

          , FORMATDATETIME(IIF(IsNothing(Fields!HistorySCRStopTime.Value) OR Fields!HistorySCRStopTime.Value="", Now(), Fields!HistorySCRStopTime.Value), DateFormat.LongTime))


    Monday, November 11, 2013 3:35 PM