SQL SERVER中强制类型转换cast和convert的区别

在SQL SERVER中,cast和convert函数都可用于类型转换,其功能是相同的,

只是语法不同.

cast一般更容易使用,convert的优点是可以格式化日期和数值.

 
   
    select
     
    CAST
    (
    '
    123
    '
     
    as
     
    int
    )   
    --
     123
    

    select
     
    CONVERT
    (
    int
    , 
    '
    123
    '
    )  
    --
     123
    

    

    select
     
    CAST
    (
    123.4
     
    as
     
    int
    )   
    --
     123
    

    select
     
    CONVERT
    (
    int
    , 
    123.4
    )  
    --
     123 
    

    

    select
     
    CAST
    (
    '
    123.4
    '
     
    as
     
    int
    )

    select
     
    CONVERT
    (
    int
    , 
    '
    123.4
    '
    )

    --
     Conversion failed when converting the varchar value '123.4' to data type int.
    

    

    select
     
    CAST
    (
    '
    123.4
    '
     
    as
     
    decimal
    )  
    --
     123
    

    select
     
    CONVERT
    (
    decimal
    , 
    '
    123.4
    '
    ) 
    --
     123 
    

    


    select
     
    CAST
    (
    '
    123.4
    '
     
    as
     
    decimal
    (
    9
    ,
    2
    ))  
    --
     123.40
    

    select
     
    CONVERT
    (
    decimal
    (
    9
    ,
    2
    ), 
    '
    123.4
    '
    ) 
    --
     123.40
    

    


    declare
     
    @Num
     
    money
    

    set
     
    @Num
     
    =
     
    1234.56
    

    select
     
    CONVERT
    (
    varchar
    (
    20
    ), 
    @Num
    , 
    0
    )  
    --
     1234.56
    

    select
     
    CONVERT
    (
    varchar
    (
    20
    ), 
    @Num
    , 
    1
    )  
    --
     1,234.56
    

    select
     
    CONVERT
    (
    varchar
    (
    20
    ), 
    @Num
    , 
    2
    )  
    --
     1234.5600