Expression for calculating the different between two dates

i am currently doing a system where user can submit their suggestions to us
and part of my system is using Reporting Services and i need some guide on my
problem.
When user submit their suggestions, the date of submission will be auto
input into SQL Server database and if the suggestions is being evaluated by a
evaluator, the date of evaluation will also be input into the SQL Server
database and all this part i had done it all in aspx.
So now when user want to view their suggestions, they will go to a page
which i had generate using Reporting Services. In this page, there is a
column call Turnaround Time(the datediff from the day of submission to now
till the suggestions is being evaluated). That means In this column i wan to
display the datediff from the date of submission till the date today if it
still had not been evaluated(that means the SQL Server database that contains
the field "Date of Evaluation" is still null). And if the suggestions had
been evaluated(that means the SQL Server database that contains the field
"Date of Evaluation" contains a date value now), it will display the datediff
from the date of submission to the date of evaluation.
For now i can only display the datediff from the date of submission to now
using the following expression : =DateDiff(DateInterval.Day,
Fields!DateSubmitted.Value, Now()) and thus i need help on how to display the
datediff from the date of submission to now if the suggestions had not been
evaluated and display the datediff from the date of submission to the date of
evaluation if the suggestions had been evaluated.
I appreciate for the help and guide. ThanksYou can try this :
IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
SubmissionDate.Value)
>--Original Message--
>i am currently doing a system where user can submit their
suggestions to us
>and part of my system is using Reporting Services and i
need some guide on my
>problem.
>When user submit their suggestions, the date of
submission will be auto
>input into SQL Server database and if the suggestions is
being evaluated by a
>evaluator, the date of evaluation will also be input into
the SQL Server
>database and all this part i had done it all in aspx.
>So now when user want to view their suggestions, they
will go to a page
>which i had generate using Reporting Services. In this
page, there is a
>column call Turnaround Time(the datediff from the day of
submission to now
>till the suggestions is being evaluated). That means In
this column i wan to
>display the datediff from the date of submission till the
date today if it
>still had not been evaluated(that means the SQL Server
database that contains
>the field "Date of Evaluation" is still null). And if the
suggestions had
>been evaluated(that means the SQL Server database that
contains the field
>"Date of Evaluation" contains a date value now), it will
display the datediff
>from the date of submission to the date of evaluation.
>For now i can only display the datediff from the date of
submission to now
>using the following expression : =DateDiff
(DateInterval.Day,
>Fields!DateSubmitted.Value, Now()) and thus i need help
on how to display the
>datediff from the date of submission to now if the
suggestions had not been
>evaluated and display the datediff from the date of
submission to the date of
>evaluation if the suggestions had been evaluated.
>I appreciate for the help and guide. Thanks
>.
>|||you can directly get the Turnaround Time from database.
In your query get this column...
DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
TurnaroundTime
and use Fields!TurnaroundTime.Value in your report.
hth
"JiaN" <JiaN@.discussions.microsoft.com> wrote in message
news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
>i am currently doing a system where user can submit their suggestions to us
> and part of my system is using Reporting Services and i need some guide on
> my
> problem.
> When user submit their suggestions, the date of submission will be auto
> input into SQL Server database and if the suggestions is being evaluated
> by a
> evaluator, the date of evaluation will also be input into the SQL Server
> database and all this part i had done it all in aspx.
> So now when user want to view their suggestions, they will go to a page
> which i had generate using Reporting Services. In this page, there is a
> column call Turnaround Time(the datediff from the day of submission to now
> till the suggestions is being evaluated). That means In this column i wan
> to
> display the datediff from the date of submission till the date today if it
> still had not been evaluated(that means the SQL Server database that
> contains
> the field "Date of Evaluation" is still null). And if the suggestions had
> been evaluated(that means the SQL Server database that contains the field
> "Date of Evaluation" contains a date value now), it will display the
> datediff
> from the date of submission to the date of evaluation.
> For now i can only display the datediff from the date of submission to now
> using the following expression : =DateDiff(DateInterval.Day,
> Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> the
> datediff from the date of submission to now if the suggestions had not
> been
> evaluated and display the datediff from the date of submission to the date
> of
> evaluation if the suggestions had been evaluated.
> I appreciate for the help and guide. Thanks|||ok thanks for your help ravi but my datatype for EvaluationDate and
SubmissionDate is Date/Time and it shows a error when i preview using the
expression you had given me.
The error is : "The value expression for the textbox 'Turnaround Time'
contains and error: [BC30452] Operator '-' is not defined for types 'Date'
and Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields"
"Ravi" wrote:
> You can try this :
> IIF(Fields!EvaluationDate.Value Is Nothing , Now()-Fields!
> SubmissionDate.Value,Fields!EvaluationDate.Value-Fields!
> SubmissionDate.Value)
>
> >--Original Message--
> >i am currently doing a system where user can submit their
> suggestions to us
> >and part of my system is using Reporting Services and i
> need some guide on my
> >problem.
> >
> >When user submit their suggestions, the date of
> submission will be auto
> >input into SQL Server database and if the suggestions is
> being evaluated by a
> >evaluator, the date of evaluation will also be input into
> the SQL Server
> >database and all this part i had done it all in aspx.
> >So now when user want to view their suggestions, they
> will go to a page
> >which i had generate using Reporting Services. In this
> page, there is a
> >column call Turnaround Time(the datediff from the day of
> submission to now
> >till the suggestions is being evaluated). That means In
> this column i wan to
> >display the datediff from the date of submission till the
> date today if it
> >still had not been evaluated(that means the SQL Server
> database that contains
> >the field "Date of Evaluation" is still null). And if the
> suggestions had
> >been evaluated(that means the SQL Server database that
> contains the field
> >"Date of Evaluation" contains a date value now), it will
> display the datediff
> >from the date of submission to the date of evaluation.
> >
> >For now i can only display the datediff from the date of
> submission to now
> >using the following expression : =DateDiff
> (DateInterval.Day,
> >Fields!DateSubmitted.Value, Now()) and thus i need help
> on how to display the
> >datediff from the date of submission to now if the
> suggestions had not been
> >evaluated and display the datediff from the date of
> submission to the date of
> >evaluation if the suggestions had been evaluated.
> >
> >I appreciate for the help and guide. Thanks
> >.
> >
>|||Its working!! Thanks avnrao!!
Ravi, thanks too! for spending the time in helping me. =)
"avnrao" wrote:
> you can directly get the Turnaround Time from database.
> In your query get this column...
> DateDiff(Day,Table.DateSubmitted,IsNull(Table.DateEvaluated,GetDate())) as
> TurnaroundTime
> and use Fields!TurnaroundTime.Value in your report.
> hth
> "JiaN" <JiaN@.discussions.microsoft.com> wrote in message
> news:37BA72F4-FCF1-4659-ACA3-9366AE53A518@.microsoft.com...
> >i am currently doing a system where user can submit their suggestions to us
> > and part of my system is using Reporting Services and i need some guide on
> > my
> > problem.
> >
> > When user submit their suggestions, the date of submission will be auto
> > input into SQL Server database and if the suggestions is being evaluated
> > by a
> > evaluator, the date of evaluation will also be input into the SQL Server
> > database and all this part i had done it all in aspx.
> > So now when user want to view their suggestions, they will go to a page
> > which i had generate using Reporting Services. In this page, there is a
> > column call Turnaround Time(the datediff from the day of submission to now
> > till the suggestions is being evaluated). That means In this column i wan
> > to
> > display the datediff from the date of submission till the date today if it
> > still had not been evaluated(that means the SQL Server database that
> > contains
> > the field "Date of Evaluation" is still null). And if the suggestions had
> > been evaluated(that means the SQL Server database that contains the field
> > "Date of Evaluation" contains a date value now), it will display the
> > datediff
> > from the date of submission to the date of evaluation.
> >
> > For now i can only display the datediff from the date of submission to now
> > using the following expression : =DateDiff(DateInterval.Day,
> > Fields!DateSubmitted.Value, Now()) and thus i need help on how to display
> > the
> > datediff from the date of submission to now if the suggestions had not
> > been
> > evaluated and display the datediff from the date of submission to the date
> > of
> > evaluation if the suggestions had been evaluated.
> >
> > I appreciate for the help and guide. Thanks
>
>