SQL Server CONVERT() Function Datetime

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