Weird division behavior - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Weird division behavior

Author

Topic

mmaisterrena
Starting Member

2 Posts

Posted - 2011-01-07 : 18:24:25

Hi to all this is my first post, hope someone can help me

Im want to create a computed column but im having trouble doing it because SQL seems to have a weird behavior on this division.

to prove my point I have done this SQL code the last 2 lines should return the same value but they are not (the last line returns 0) , any other operation besides division will return the correct value but not the division, can some one please help me

DECLARE @INICIO_PROG DATETIME;
DECLARE @TERMINO_PROG DATETIME;
DECLARE @DT float;
DECLARE @DP float;


SET @INICIO_PROG = '2011-01-04';
SET @TERMINO_PROG = '2011-01-17';

SET @DT = DATEDIFF(day, @INICIO_PROG, GETDATE())+1; 
SET @DP = DATEDIFF(day, @INICIO_PROG, @TERMINO_PROG)+1; 

SELECT (@DT/@DP)*100
SELECT ((DATEDIFF(day, @INICIO_PROG, GETDATE())+1)/(DATEDIFF(day, @INICIO_PROG, @TERMINO_PROG)+1))*100

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-07 : 18:42:12

A few ways to handle this. Here's one:


DECLARE @INICIO_PROG DATETIME;
DECLARE @TERMINO_PROG DATETIME;
DECLARE @DT float;
DECLARE @DP float;


SET @INICIO_PROG = '2011-01-04';
SET @TERMINO_PROG = '2011-01-17';

SET @DT = DATEDIFF(day, @INICIO_PROG, GETDATE())+1; 
SET @DP = DATEDIFF(day, @INICIO_PROG, @TERMINO_PROG)+1; 

SELECT (@DT/@DP)*100
SELECT ((DATEDIFF(day, @INICIO_PROG, GETDATE())+1.0)/(DATEDIFF(day, @INICIO_PROG, @TERMINO_PROG)+1))*100

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-07 : 18:44:01

The math is correct, but your Select statement is creating values which are whole integers, so it is multiplying the result of 0 * 100 and presents the correct result.

SELECT (CONVERT(float,(DATEDIFF(day, @INICIO_PROG, GETDATE())+1)))/(CONVERT(float,(DATEDIFF(day, @INICIO_PROG, @TERMINO_PROG)+1)))*100

Or do what Russel said ;) which is easier.

Poor planning on your part does not constitute an emergency on my part.

mmaisterrena
Starting Member

2 Posts

Posted - 2011-01-07 : 18:46:45

Thank you very much for your help, they both work great!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources