CODEWITHSUNDEEP
sql-server
Daniel
Daniel

Reputation: 2295

Not Getting Expected Value when Performing Calculation on Date-based variables

I'm trying to get a decimal value of the DaysRemainingInMonth / DaysInMonth in a SELECT statement, however I always get a 0 value in the column when executing the statement.

It seems so strange since it's essentially a simple division exercise:

declare @DaysinMonth int = DAY(EOMONTH(GETDATE())) -- 31
declare @DaysRemaining int = @DaysinMonth - DAY(GETDATE()) -- 24

SELECT 
      (DAY(EOMONTH(GETDATE())) - DAY(GETDATE())) as DaysRemaining
      ,@DaysinMonth as DaysinMonth 
      ,@DaysRemaining as DaysRemaining2
      , cast( (24/31) as decimal(18,10)) as Col1 -- Try casting and hard-coded values??
      , @DaysRemaining / @DaysinMonth as col2
      , 10 / 2 as WorkingExample

Surely I should get 0.774194... as an answer for Col1 at the very least? Am I missing something obvious?

Upvotes: 0

Views: 39

Answers (3)

Zhorov
Zhorov

Reputation: 29943

The reason for your unexpected results is the implicit data type conversion and when you divide two integer values, the result will be also an integer.

You may try with the following code (again with implicit data type conversion made by the server):

declare @DaysinMonth int = DAY(EOMONTH(GETDATE())) -- 31
declare @DaysRemaining int = @DaysinMonth - DAY(GETDATE()) -- 24

SELECT 
    (DAY(EOMONTH(GETDATE())) - DAY(GETDATE())) as DaysRemaining,
    @DaysinMonth as DaysinMonth,
    @DaysRemaining as DaysRemaining2, 
    (1.0 * 24 / 31) as Col1,
    1.0 * @DaysRemaining / @DaysinMonth as col2,
    10 / 2 as WorkingExample

Or use explicit data type conversion:

declare @DaysinMonth int = DAY(EOMONTH(GETDATE())) -- 31
declare @DaysRemaining int = @DaysinMonth - DAY(GETDATE()) -- 24

SELECT 
    (DAY(EOMONTH(GETDATE())) - DAY(GETDATE())) as DaysRemaining,
    @DaysinMonth as DaysinMonth,
    @DaysRemaining as DaysRemaining2, 
    CONVERT(decimal, 24) / 31  as Col1,
    CONVERT(decimal, @DaysRemaining) / @DaysinMonth as col2,
    10 / 2 as WorkingExample

Output:

DaysRemaining   DaysinMonth DaysRemaining2  Col1     col2           WorkingExample
24              31          24              0.774193 0.77419354838  5

Upvotes: 1

Andrew
Andrew

Reputation: 5277

Try casting both the values to numeric

declare @DaysinMonth int = DAY(EOMONTH(GETDATE())) -- 31
declare @DaysRemaining int = @DaysinMonth - DAY(GETDATE()) -- 24

SELECT 
      (DAY(EOMONTH(GETDATE())) - DAY(GETDATE())) as DaysRemaining
      ,@DaysinMonth as DaysinMonth 
      ,@DaysRemaining as DaysRemaining2
      , cast( (24/31) as decimal(18,10)) as Col1 -- Try casting and hard-coded values??
      , cast(@DaysRemaining as numeric) / cast(@DaysinMonth as numeric) as col2
      , 10 / 2 as WorkingExample

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

As both operands are integers, the result is also an integer with the fractional part cut off.

Convert one (or all) of the operands to decimal, so that the result is also a decimal.

...
, convert(decimal, 24) / 31 as Col1
, convert(decimal, @DaysRemaining) / @DaysinMonth as col2
...

Upvotes: 1

Related Questions