数据库

Sql server 日期截取

2010-10-02

CONVERT的使用方法:   
    
    ////////////////////////////////////////////////////////////////////////////////////////   
    
    格式:   
    CONVERT(data_type,expression[,style])   
    
    说明:   
    此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)   
    相互转换的时候才用到.   
    
    例子:   
    SELECT     CONVERT(varchar(30),getdate(),101)     now   
    结果为   
    now   
    ---------------------------------------   
    09/15/2001   
    
    /////////////////////////////////////////////////////////////////////////////////////   
    
    style数字在转换时间时的含义如下   
    
    -------------------------------------------------------------------------------------------------   
    Style(2位表示年份)         |       Style(4位表示年份)           |         输入输出格式                                                                           
    -------------------------------------------------------------------------------------------------   
    -                                           |       0     or     100                               |         mon     dd     yyyy     hh:miAM(或PM)                               
    -------------------------------------------------------------------------------------------------   
    1                                           |       101                                         |         mm/dd/yy                                                                                 
    -------------------------------------------------------------------------------------------------   
    2                                           |       102                                         |         yy-mm-dd                                                                                   
    -------------------------------------------------------------------------------------------------   
    3                                           |       103                                         |         dd/mm/yy                                                                                 
    -------------------------------------------------------------------------------------------------   
    4                                           |       104                                         |         dd-mm-yy                                                                                   
    -------------------------------------------------------------------------------------------------   
    5                                           |       105                                         |         dd-mm-yy                                                                                   
    -------------------------------------------------------------------------------------------------   
    6                                           |       106                                         |         dd     mon     yy                                                                                   
    -------------------------------------------------------------------------------------------------   
    7                                           |       107                                         |         mon     dd,yy                                                                                   
    -------------------------------------------------------------------------------------------------   
    8                                           |       108                                         |         hh:mm:ss                                                                                     
    -------------------------------------------------------------------------------------------------   
    -                                           |       9     or     109                               |         mon     dd     yyyy     hh:mi:ss:mmmmAM(或PM)   
    -------------------------------------------------------------------------------------------------   
    10                                         |       110                                         |         mm-dd-yy                                                                                     
    -------------------------------------------------------------------------------------------------   
    11                                         |       111                                         |         yy/mm/dd                                                                                   
    -------------------------------------------------------------------------------------------------   
    12                                         |       112                                         |         yymmdd                                                                                         
    -------------------------------------------------------------------------------------------------   
    -                                           |       13     or     113                             |         dd     mon     yyyy     hh:mi:ss:mmm(24小时制)       
    -------------------------------------------------------------------------------------------------   
    14                                         |       114                                         |         hh:mi:ss:mmm(24小时制)                                           
    -------------------------------------------------------------------------------------------------   
    -                                           |       20     or     120                             |               yyyy-mm-dd     hh:mi:ss(24小时制)                     
    -------------------------------------------------------------------------------------------------   
    -                                           |       21     or     121                             |               yyyy-mm-dd     hh:mi:ss:mmm(24小时制)     
    -------------------------------------------------------------------------------------------------