SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


expression output


expression output

Author
Message
niha.736
niha.736
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 233
Hi,
can any body tell the output for expression by taking any 2 dates as example.

=cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1)


Thanks,
Niha
davoscollective
davoscollective
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 1004
=cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1)

The DateDiff function "Returns a Long value specifying the number of time intervals between two Date values."

DateInterval.Day is the Day member of the DateInterval collection, so the DateDiff function will return the number of days between the two dates, actually the number of Day "boundaries". By boundaries I mean if you gave it 11pm on day one and 1am the next day it would still return 1 day.

The formula is then converting or as someone once said to me "Coercing into a decimal" using the Cdec() function. i.e. it is converting the Long value returned by the DateDiff function into a Decimal value. I won't go into the difference between Long and Decimal datatypes here, suffice to say Long is a floating point type and decimal is an exact numeric type - exacts are generally safer to work with, but this conversion is probably unecessary in this instance because giving DateDiff function a Day interval is only ever going to return integers (not the datetype).

After the Cdec() function, the formula is then multiplying by -1. This is a common trick to convert positive numbers to negative, or negative numbers to positive. For some reason the author of the formula wants to change the sign of the returned value.

One flaw with this formula is that it assumes the date1 and date2 are always in a certain order. For example if Date1 is usually earlier than Date2, this formula will return a negative value because it is being multiplied by -1.

I don't think I can explain this more thoroughly, but it would be preferable if you explained why you need to understand this formula, especially the context of its use.

If you gave the formula these two dates 2013-02-01 and 2013-02-2 as date1 and date2 it would give you a result of -1.

If you gave it those two dates the other way around it would give you +1.
niha.736
niha.736
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 233
Thanks for your time and explanation.

Here I want to share my issue,
Day1: 09.01.2013 (dd.mm.yyyy)
Day2: 08.01.2013 (dd.mm.yyyy)

Datediff column has to show the difference between dates as per the below expression ,

Expression: “ =Cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1)”

But it is taking the above dates as 09.01.2013(mm.dd.yyyy) & 08.01.2013 (mm.dd.yyyy) as per the values of datediff, I have replaced the above expression with Year and checked values. It has to give all ‘Zero’ values as passing year is same for Date1,Date2 fields.
Still it is showing the errors.

“ =Cdec(DateDiff(DateInterval.Year,Fields!Date.Value,Fields!Date2.Value))*(-1)”

any Help would be appreciated..
Thanks,
Niha
davoscollective
davoscollective
SSC Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 Visits: 1004
Hi Niha,
Can you please show the errors here.

Datediff will show you the number of intervals. If you want days, leave it as days.

If your input dates,
Fields!Date.Value

&
Fields!Date2.Value

are from a database, and they are datetime datatypes, then Datediff will give you the correct result; the number of days between the two dates.

It won't assume dd.mm.yyyy or mm.dd.yyyy

If your two fields are not coming from a database, for example if they are text parameters then it might have problems.


Can you please explain your problem a bit more, where your fields come from, and what the error is?
MMartin1
MMartin1
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2817 Visits: 2031
Check the default language of the login accessing the data for your report. British English and English will interpret '08-01-2013' in different ways.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search