Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

expression output Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 11:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, 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
Post #1419822
Posted Sunday, February 17, 2013 11:20 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 471, Visits: 875
=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.

Post #1421038
Posted Monday, February 18, 2013 9:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, 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
Post #1421415
Posted Monday, February 18, 2013 9:18 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 471, Visits: 875
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?

Post #1421419
Posted Thursday, May 9, 2013 10:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
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.
Post #1451267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse