SQLServerCentral » SQL Server 2005 » Business Intelligence » expression outputInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralThu, 19 Jan 2017 06:15:13 GMT20expression outputhttps://www.sqlservercentral.com/Forums/FindPost1419822.aspxHi,
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,
NihaThu, 09 May 2013 10:50:39 GMTniha.736RE: expression outputhttps://www.sqlservercentral.com/Forums/FindPost1451267.aspxCheck the default language of the login accessing the data for your report. British English and English will interpret '08-01-2013' in different ways.Thu, 09 May 2013 10:50:39 GMTMMartin1RE: expression outputhttps://www.sqlservercentral.com/Forums/FindPost1421419.aspxHi 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, [code="sql"]Fields!Date.Value[/code] & [code="sql"]Fields!Date2.Value[/code] are from a database, and they are [i]datetime [/i] 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?
Mon, 18 Feb 2013 21:18:27 GMTdavoscollectiveRE: expression outputhttps://www.sqlservercentral.com/Forums/FindPost1421415.aspxThanks 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,
NihaMon, 18 Feb 2013 21:00:40 GMTniha.736RE: expression outputhttps://www.sqlservercentral.com/Forums/FindPost1421038.aspx=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.
Sun, 17 Feb 2013 23:20:53 GMTdavoscollective