Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2957
  • Last Modified:

Need to get the number/count of the days between two date values in my DB(SSRS)

I am trying to show the number of days between my PlannedStartDate and my ActualEndDate

I need a count of the days. Can someone tell me how that would look?

I know that I will probably need to use a construct like Count(DateDiff(between ActualEndDate + PlannedStartDate))

I don't know exactly how to structure it. Any help is appreciated!
0
J C
Asked:
J C
  • 6
  • 4
1 Solution
 
ThomasianCommented:
SELECT DATEDIFF(DAY,PlannedStartDate,ActualEndDate)

Open in new window

If you want the total number of dates for a group, use SUM
SELECT SUM(DATEDIFF(DAY,PlannedStartDate,ActualEndDate))

Open in new window

0
 
J CAuthor Commented:
I tried creating a calculated field inside of the column but it only printed the text of the query above. It doesn't return the number of days between the PlanStartDate and ActualEndDate. I need this to act as an aging mechanism to show how many days past the original date for this task that it was completed. I tried using the below as an expression within the report. It accepted my syntax but prints as #Error

=DateDiff("Day",Fields!planstartdate.Value,Fields!actualenddate.Value)

Should the above work?
0
 
J CAuthor Commented:
This is the error I receive.


Warning      1      [rsRuntimeErrorInExpression] The Value expression for the textrun ‘actualenddate.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Interval' is not a valid value.      C:\Users\administrator.MyDomain\Documents\Visual Studio 2008\Projects\ScheduleTrackingMatrix\ScheduleTrackingMatrix\Schedule Tracking Matrix.rdl      0      0
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ThomasianCommented:
Use "d" instead of "day"
0
 
J CAuthor Commented:
Ah, that is progress. It is showing me the number of days for several records but on many I am also seeing a strange value.

-734738, several like this.

Ever run across anything like that?
0
 
J CAuthor Commented:
Doh, I think I see what is happening. There is no ActualEndDate for those records that are displaying funky numbers. Probably need to use an IIF construct
0
 
ThomasianCommented:
Show the values of the 2 fields you are computing with to check where the values are coming from.
0
 
ThomasianCommented:
Yes, you can show nothing if any of the 2 fields is null
=IIF(ISNULL(Fields!planstartdate.Value) OR ISNULL(Fields!actualenddate.Value), Nothing, DateDiff("d",Fields!planstartdate.Value,Fields!actualenddate.Value))

Open in new window

0
 
J CAuthor Commented:
=IIF(IsNothing(Fields!actualenddate.Value),Nothing,Fields!DaysDiff.Value)

This is what I ended up with that works great. Thanks for setting me on the correct path!
0
 
J CAuthor Commented:
Thanks a lot!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now