Convert in SQL Server

Convert in SQL Server  2012

Converts one data type to another in SQL Server

Convert  char to  numeric

             select convert(decimal(5,2),'10.345666'),
                           convert(numeric(5,2),'10.3456788')

             O/P :   10.35  10.35

i.e  decimal/numeric in total  can contain 5 digits(p) in that 2 (s) for decimals.
p: precision total digits left and right of decimal digit
s: scale  it can scale upto 2 digits.

Convert char to Money

              money has max 4 decimal digits, so it will be rounded.

             select convert(money,'10.345666'), convert(smallmoney,'10.345666')

             O/P: 10.3457   10.3457

Convert char to TinyInt

                    TinyInt occupies values ranging from 0 to 255.

                    select convert(tinyint,'0'),convert(tinyint,'255')  //O/P:   0  255
                    select convert(tinyint,'256')
                     O/P:
                               The conversion of the varchar value '256' overflowed an INT1 column. Use a larger integer column.

Convert char to smallint

  Smallint occupies values ranging from -32768 to 32767.

                    select convert(smallint,'0'),convert(smallint,'32767')  //O/P:   0  255
                    select convert(smallint,'32768')
                     O/P:
                               The conversion of the varchar value '32768' overflowed an INT2 column. Use a larger integer column.

Convert char to int

  Int datatype occupies values ranging from -2147483648to 2147483647.

                    select convert(int,'0'),convert(int,'2147483647'),convert(int, '-2147483648') 
                    //O/P:   0  2147483647 -2147483648
                    select convert(int,'2147483648')
                     O/P:
                              The conversion of the varchar value '2147483648' overflowed an int column.
 Note:Buffer Overflow.

 

Convert char to bigint

      bigint datatype occupies values randing from -9223372036854775808  to 9223372036854775807            

                    select convert(int,'0'),convert(int,'9223372036854775807'),
                    convert(int,'-9223372036854775807') 
                    //O/P:   0    9223372036854775807    -9223372036854775808
                   select  convert(bigint,'9223372036854775808')
                     O/P:
                              Arithmetic overflow error converting expression to data type bigint.

 

Tags:Convert in SQL Server ,Convert  char to  numeric,

Convert  char to  numeric,convert char to smallmoney,

convert  char to money,convert char to tinyint,

convert char to smallint,

convert char to int,

convert char to bigint