Dates in Reporting Services

hey all

set up

Visual Studio 2005

SQL Server Express / Reporting Services

four fields

State date Start time Finish Date Finish Time

I need to take one away from the other - can someone please help me?

Is it better to keep these in separate fields or to combine and subtract?

Is there anything special I need to know with subtracting time?

I am reasonably newbie still so would appreciate any help thanks

I am using the visual side in Reporting services - Data - Layout - Preview.

thanks

Jewel

Jewel,

What you are describing could be done a couple different ways, but the best method would likely depend on your situation. Can you provide a bit more detail or maybe an example of what you would expect to happen?

|||

thanks heaps for replying

so I would have a line like this - other info eg Tracker / Description etc would be on the 2nd line

and Criteria would be = Closed

Call ID Received Date Received Time Closed Date Closed Time TIME TAKEN

so Time Taken would be the result from Received to Closed.

thanks

Jewel

|||

To come up with Time Taken in this case, I would probably combine your start date and time into one variable, then combine your end date and time into another variable and perform your subtraction from there. So if you had the following date initially:

Received Date: 01/12/2006
Received Time: 18:23:47
Closed Date: 01/13/2006
Closed Time: 06:35:27

Then you would have two new fields as follows:

Received DateTime: 01/12/2006 18:23:47
Closed DateTime: 01/13/2006 06:35:27

You would then subtract one from the other to get the time difference.

|||

thanks

so I have combined my fields as you said.

When I do the subtraction -

New fields - Text=ClosedDate Text=RecvdDate

=(ClosedDate) - (RecvdDate)

I get error ClosedDate not declared

or should I be using?

thanks

|||

it depends where you combine the fields. You can either do this in you source query or using the .NET object model in an expression. Either way you need to make sure the field has the correct datatype

SQL
===

SELECT ReceivedDateTime = CAST(ReceivedDate + ' ' + ReceivedTime AS DATETIME)
, ClosedDateTime = CAST(ClosedDate + ' ' + ClosedTime AS DATETIME)
FROM your_table

Expression (assuming your fields are string data type)

=CDate(Fields!ClosedDate.Value + " " + Fields!ClosedTime.Value) - CDate(Fields!ReceivedDate.Value + " " + Fields!ReceivedTime.Value)

|||

thanks Adam

that works a treat - appreciate it