help conversion problem  
Author Message
khcharles





PostPosted: Tue Aug 05 06:57:57 CDT 2003 Top

SQL Server Developer >> help conversion problem

Hi All,

I want to convert a character data type into numeric but im having problem
when dividing a number which is less than the denominator. It returns 0.
what seems to be the problem. here is my query...


CONVERT(int, SUM(Duration/60)) As TotalMins

SQL Server177  
 
 
Jacco





PostPosted: Tue Aug 05 06:57:57 CDT 2003 Top

SQL Server Developer >> help conversion problem Hi Joel,

Duration/60 already does an implicit conversion of Duration to an INT
datatype. And if you divide an int by an int, the result will always be
rounded down.

There are a few options that will return the result you expect:
CONVERT(int, SUM(Duration/60.)) As TotalMins -- Note the dot.
CONVERT(int, SUM(CAST(Duration AS FLOAT)/60)) As TotalMins
SUM(CAST(Duration AS INT))/60 As TotalMins


--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.




> Hi All,
>
> I want to convert a character data type into numeric but im having problem
> when dividing a number which is less than the denominator. It returns 0.
> what seems to be the problem. here is my query...
>
>
> CONVERT(int, SUM(Duration/60)) As TotalMins
>
>


 
 
praveen





PostPosted: Tue Aug 05 07:02:41 CDT 2003 Top

SQL Server Developer >> help conversion problem
Try this

CONVERT(int, SUM(Duration/60.0)) As TotalMins

Praveen Maddali
MCDBA, MCSD



> Hi All,
>
> I want to convert a character data type into numeric but im having problem
> when dividing a number which is less than the denominator. It returns 0.
> what seems to be the problem. here is my query...
>
>
> CONVERT(int, SUM(Duration/60)) As TotalMins
>
>


 
 
Joel





PostPosted: Tue Aug 05 07:26:12 CDT 2003 Top

SQL Server Developer >> help conversion problem thanks for your immediate response. i tried what were stated below but i got
an error message:

'Arithmetic overflow error converting numeric to datat type numeric'

thanks again,
joel



> Hi Joel,
>
> Duration/60 already does an implicit conversion of Duration to an INT
> datatype. And if you divide an int by an int, the result will always be
> rounded down.
>
> There are a few options that will return the result you expect:
> CONVERT(int, SUM(Duration/60.)) As TotalMins -- Note the dot.
> CONVERT(int, SUM(CAST(Duration AS FLOAT)/60)) As TotalMins
> SUM(CAST(Duration AS INT))/60 As TotalMins
>
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
>


> > Hi All,
> >
> > I want to convert a character data type into numeric but im having
problem
> > when dividing a number which is less than the denominator. It returns 0.
> > what seems to be the problem. here is my query...
> >
> >
> > CONVERT(int, SUM(Duration/60)) As TotalMins
> >
> >
>
>