0

I need to convert a calculation from VBS to Sql server to the whole number and the nearest tenth. This is the calculation on VB: 

 a=2/28/2016
    b=6/15/2016
    d=1
    Days360(a, b) = 107
    -INT(-(10^d)*DAYS360(a,b)/30)/(10^d) =
    -INT(-(10^1)*107/30)/(10^1) =
    -INT(-10*107/30)/10 =
    -INT(-1070/30)/10 =
    -INT(-35.666667)/10 =
    -(-36)/10 =
    36/10 = 3.6 --- result for nearest tenth

a=2/28/2016
b=6/15/2016
d=0
Days360(a, b) = 107
-INT(-(10^d)*DAYS360(a,b)/30)/(10^d) =
-INT(-(10^0)*107/30)/(10^0) =
-INT(-1*107/30)/1 =
-INT(-107/30)/1 =
-INT(-3.5666667)/1 =
-(-4)/1 =
4/1 =4  ---result for the whole number

How do I convert these two to sql? I use these two queries but I don't get the results I expected:

SELECT ROUND(DATEDIFF(day,'2016-02-28','2016-06-15')*10/30/10,0) AS DiffDate
SELECT ROUND(DATEDIFF(day,'2016-02-28','2016-06-15')*10/30/10,1) AS DiffDate
macoms01
  • 1,110
  • 13
  • 22
angelcake
  • 119
  • 5
  • 7
  • 18

1 Answers1

1

Some versions of SQL server have the following behaviour when dealing with literals:

Any non-enclosed numeral with no decimal point is an integer constant. When dividing two integers, the result is by default an integer, so the expression might get evaluated to 107 * 10 / 30 / 10 => 1070 / 30 / 10 => 35 / 10 => 3 with the usual precedence rules. Try using floating-point constants and observe the results. (E.g. write 10.0 instead of 10 )

See also this page for more information about constants: https://msdn.microsoft.com/en-us/library/ms179899.aspx. Or see this SO question: How to get a float result by dividing two integer values?

Community
  • 1
  • 1
aphid
  • 1,135
  • 7
  • 20
  • This is true but you don't have to add decimal places to every literal in the calculation. Just adding a decimal point after the first 10 is adequate for this. SELECT ROUND(DATEDIFF(day,'2016-02-28','2016-06-15')*10./30/10,0) AS DiffDate – Sean Lange Jan 22 '15 at 20:41
  • SELECT ROUND(DATEDIFF(day,'2016-02-28','2016-06-15')*10./30/10,0) AS DiffDate gives me the result "4.000000000" – angelcake Jan 22 '15 at 21:08
  • This query gives me the result "4": SELECT ROUND(DATEDIFF(day,'2016-02-28','2016-06-15')*CAST(10 AS float)/CAST(30 AS float)/CAST(10 AS float),0) AS DiffDate – angelcake Jan 22 '15 at 21:09