Post reply

DATEDIFF Issue

  • November 8, 2013 at 12:31 pm

    #395794

    I have the following SQL query in my dataset

    SELECT DATEDIFF(m, CarsDate, GETDATE()) AS Month

    FROM dbo.Cars

    It returns 53 months

    When I use an expression in a textbox with this dataset, it shows me the date 53 months ago - instead of the number 53 which is what I want.

    How do I overcome this?

  • November 8, 2013 at 4:09 pm

    #1665246

    I also tried this:

    =DATEDIFF(DateInterval.Month,FORMATDATETIME(Now(),2),FORMATDATETIME(Fields!Date.Value,2))

    and

    =DATEDIFF(DateInterval.Month,Format(Now(), "MM/dd/yyyy hh:mm:ss tt"),Fields!Date.Value)

    I get this:

    Overload resolution failed because no accessible 'DateDiff' can be called without a narrowing conversion:

  • November 8, 2013 at 4:28 pm

    #1665248

    SELECT cast( DATEDIFF(m, CarsDate, GETDATE()) as int )

    Or perhaps if you just want to return a number create a function or perhaps populate a variable

    ***The first step is always the hardest *******

  • November 8, 2013 at 5:08 pm

    #1665258

    Even with the CAST as INT I still got errors in SSRS

    What I was trying to accomplish was get the dates between TODAY and past average date. Then divide by 12 months to find out how may years with a decimal point. 2.18 years

    This finally worked below. Trial and error.

    =ROUND((DATEDIFF(DateInterval.Month, First(Fields!Date.Value, "DataSet2"), NOW() ))/12,2)

  • November 8, 2013 at 5:12 pm

    #1665261

    Datediff always returns an integer, in T-SQL and in the SSRS function so I'm not sure how you are getting what you describe.

    For the SSRS expression use Today() rather then Now() if you don't want the time part of the datetime (Today() gives you the date as at 00:00:00).

    Also you shouldn't be casting your dates to strings which is what the Format functions are doing and is causing the error. DateDiff only expects datetimes.

    So try:

    =DateDiff(DateInterval.Month, Today(), Fields!Date.Value)

    Your T-SQL statement looks fine and does not need casting. If it's returning an integer when you run it in management studio, that is what it will be sending to reporting services.

  • November 8, 2013 at 6:57 pm

    #1665278

    I am having trouble replicating this.

    I have tried what you suggested

    My Dataset3 query returns: 03/10/2010 9:58:24 PM

    =DateDiff(DateInterval.Month, Today(), First(Fields!Date.Value,"Dataset3"))

    I get: Conversion from type 'Integer' to type 'Date' is not valid

  • November 8, 2013 at 7:19 pm

    #1665279

    UPDATE:

    I changed my SQL query

    SELECT cast(CarsDate as Datetime) as Mon

    FROM CARS

    Then in SSRS textbox

    =ROUND(Datediff(Dateinterval.month,First(Fields!mon.value,"Dataset3"),Today() )/12,2)

    Seems to work now

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

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