CODEWITHSUNDEEP
reporting-servicesssrs-2012
Philip Herman
Philip Herman

Reputation: 117

Getting difference in 2 dates in SSRS with dates in int form

Just a quick question. I have to calculate the difference between two dates in an SSRS report. The issue is that the dates are just integers (19740815, 20150924). I have tried converting to date and formatting the data to datetime but with no luck. I cannot change the data in the database so I need to figure out a way to do the calculations without changing the way it displays on the report. Any help would be appreciated.

Upvotes: 0

Views: 407

Answers (2)

Thang Mai
Thang Mai

Reputation: 141

You can parse those value into date in ssrs then use the datediff function, ex

=DateDiff("d",cdate(Mid("19740815",5,2) & "/" & Mid("19740815",7,2) & "/" & Mid("19740815",1,4)),cdate(Mid("20150924",5,2) & "/" & Mid("20150924",7,2) & "/" & Mid("20150924",1,4)))

Upvotes: 1

Hannover Fist
Hannover Fist

Reputation: 10860

To convert your field to a date, use: 

CDATE(MID(Fields!YourDate.Value, 5, 2) & "/" & MID(Fields!YourDate.Value, 7, 2) & "/" & LEFT(Fields!YourDate.Value, 4))

This is if your date setting use MM/DD/YYYY, If you use DD/MM/YYYY, switch the 5 and 7 in the MIDs.

Your date DIFF would end up like 

=DATEDIFF("D", 
 CDATE(MID(Fields!YourDate.Value, 5, 2) & "/" & MID(Fields!YourDate.Value, 7, 2) & "/" & LEFT(Fields!YourDate.Value, 4)),
 CDATE(MID(Fields!OtherDate.Value, 5, 2) & "/" & MID(Fields!OtherDate.Value, 7, 2) & "/" & LEFT(Fields!OtherDate.Value, 4)) )

I would create a Calculated Field that does the conversion so you can use your new field without having to put an expression to convert it every where.

=DATEDIFF("D", Fields!NewDateField.Value, Fields!OtherNewDate.Value)

Upvotes: 1

Related Questions