SQL Server CONVERT() Function Datetime

Thảo luận trong 'Kiến thức lập trình SQL & MySQL' bắt đầu bởi admin, 13/12/13.

  1. admin

    admin Phạm Công Sơn Thành viên BQT

    Tham gia ngày:
    22/5/13
    Bài viết:
    3,741
    Đã được thích:
    715
    Điểm thành tích:
    113
    Giới tính:
    Nam
    Definition and Usage

    The CONVERT() function is a general function that converts an expression of one data type to another.
    The CONVERT() function can be used to display date/time data in different formats.
    Syntax

    CONVERT(data_type(length),expression,style)


    [TABLE="class: reference notranslate, width: 821"]
    [TR="bgcolor: #F6F4F0"]
    [/TR]

    data_type(length)
    Specifies the target data type (with an optional length)
    [TR="bgcolor: #F6F4F0"]
    expression
    Specifies the value to be converted
    [/TR]

    style
    Specifies the output format for the date/time
    [/TABLE]

    The table below represent the style values for datetime or smalldatetime conversion to character data:
    [TABLE="class: reference notranslate, width: 821"]
    [TR="bgcolor: #F6F4F0"]
    [/TR]

    -
    0 or 100
    mon dd yyyy hh:miAM (or PM)
    Default
    [TR="bgcolor: #F6F4F0"]
    1
    101
    mm/dd/yy
    USA
    [/TR]

    2
    102
    yy.mm.dd
    ANSI
    [TR="bgcolor: #F6F4F0"]
    3
    103
    dd/mm/yy
    British/French
    [/TR]

    4
    104
    dd.mm.yy
    German
    [TR="bgcolor: #F6F4F0"]
    5
    105
    dd-mm-yy
    Italian
    [/TR]

    6
    106
    dd mon yy

    [TR="bgcolor: #F6F4F0"]
    7
    107
    Mon dd, yy

    [/TR]

    8
    108
    hh:mm:ss

    [TR="bgcolor: #F6F4F0"]
    -
    9 or 109
    mon dd yyyy hh:mi:ss:mmmAM (or PM)
    Default+millisec
    [/TR]

    10
    110
    mm-dd-yy
    USA
    [TR="bgcolor: #F6F4F0"]
    11
    111
    yy/mm/dd
    Japan
    [/TR]

    12
    112
    yymmdd
    ISO
    [TR="bgcolor: #F6F4F0"]
    -
    13 or 113
    dd mon yyyy hh:mi:ss:mmm (24h)

    [/TR]

    14
    114
    hh:mi:ss:mmm (24h)

    [TR="bgcolor: #F6F4F0"]
    -
    20 or 120
    yyyy-mm-dd hh:mi:ss (24h)

    [/TR]

    -
    21 or 121
    yyyy-mm-dd hh:mi:ss.mmm (24h)

    [TR="bgcolor: #F6F4F0"]
    -
    126
    yyyy-mm-ddThh:mi:ss.mmm (no spaces)
    ISO8601
    [/TR]

    -
    130
    dd mon yyyy hh:mi:ss:mmmAM
    Hijiri
    [TR="bgcolor: #F6F4F0"]
    -
    131
    dd/mm/yy hh:mi:ss:mmmAM
    Hijiri
    [/TR]
    [/TABLE]


    [HR][/HR]Example

    The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:
    CONVERT(VARCHAR(19),GETDATE())
    CONVERT(VARCHAR(10),GETDATE(),10)
    CONVERT(VARCHAR(10),GETDATE(),110)
    CONVERT(VARCHAR(11),GETDATE(),6)
    CONVERT(VARCHAR(11),GETDATE(),106)
    CONVERT(VARCHAR(24),GETDATE(),113)


    The result would look something like this:
    Nov 04 2011 11:45 PM
    11-04-11
    11-04-2011
    04 Nov 11
    04 Nov 2011
    04 Nov 2011 11:45:34:243

     
    Cảm ơn đã xem bài:

    SQL Server CONVERT() Function Datetime

    Đang tải...


Like và Share ủng hộ ITSEOVN