Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEDIFF Issue Expand / Collapse
Author
Message
Posted Friday, November 08, 2013 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 5:45 AM
Points: 33, Visits: 81
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?

Post #1512790
Posted Friday, November 08, 2013 4:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 5:45 AM
Points: 33, Visits: 81
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:
Post #1512840
Posted Friday, November 08, 2013 4:28 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 268, Visits: 993
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 is always the hardest
Post #1512842
Posted Friday, November 08, 2013 5:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 5:45 AM
Points: 33, Visits: 81

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)
Post #1512854
Posted Friday, November 08, 2013 5:12 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 5:58 AM
Points: 75, Visits: 624
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.

Post #1512857
Posted Friday, November 08, 2013 6:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 5:45 AM
Points: 33, Visits: 81
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



Post #1512875
Posted Friday, November 08, 2013 7:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 5:45 AM
Points: 33, Visits: 81
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
Post #1512876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse