DATEDIFF Issue

Question

Post reply

DATEDIFF Issue

TJT

SSCommitted

Points: 1926
More actions
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?

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

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

TJT SSCommitted Points: 1926 More actions · Answer 1

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:

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

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 *******

TJT SSCommitted Points: 1926 More actions · Answer 3

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)

Spiff SSCertifiable Points: 7766 More actions · Answer 4

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.

TJT SSCommitted Points: 1926 More actions · Answer 5

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

TJT SSCommitted Points: 1926 More actions · Answer 6

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