0

The DateDiff = 23 so 23/30 is 0.77.

I cannot get @Tenor to be 1, which is 0.77 rounded ... it keeps giving me 0 ... 

DECLARE @Tenor Decimal(18,6)
SET     @Tenor =  ROUND(DATEDIFF(D,'2014-04-14','2014-05-07') / 30, 0)

It works if I do this ... this will give me 1. But I need to use the method above because it's all in an UPDATE statement. Tips appreciated, thanks.

DECLARE @Tenor Decimal(18,6)
DECLARE @dd decimal(18, 6)
SELECT @dd = DATEDIFF(D, '2014-04-14', '2014-05-07') 
SET @Tenor = Round(@dd/30,0)
nanonerd
  • 1,964
  • 6
  • 23
  • 49

2 Answers2

2

Use 30.0 in your division to force non-integer arithmetic

DECLARE @Tenor Decimal(18,6)
SET     @Tenor =  ROUND(DATEDIFF(D,'2014-04-14','2014-05-07') / 30.0, 0)
SELECT  @Tenor -- 1.000000

The behaviour you are seeing is as a result of implicit type conversion in SQL Server. Both the return type of DATEDIFF(D,'2014-04-14','2014-05-07') and 30 are int, so integer division will occur when dividing the two (effectively removing the fractional part of the result, i.e. 0).

In your second attempt the dividend (@dd) is defined as decimal which results an implicit cast of the divisor (30) to decimal, giving the result you'd expect.

Pero P.
  • 25,813
  • 9
  • 61
  • 85
1
SELECT @Tenor =  ROUND((CAST(DATEDIFF(D,'2014-04-14','2014-05-07') AS DECIMAL(18,6)) / 30), 0)

This should work to round it to 1.

Frank
  • 658
  • 1
  • 8
  • 12
  • actually, looking at it again, I do not want to use Ceiling ... but the casting to Decimal part might work (w/o the Ceiling). – nanonerd May 15 '14 at 07:01
  • Yes, you're correct it does work without the CEILING. I updated my answer. – Frank May 15 '14 at 15:21