CONVERT (TSQL Function)
Converts the argument to the specified datatype.
Syntax
CONVERT ( datatype , expression [, style] )
Arguments
- datatype
- is the datatype the expression will be converted to.
- The default precision for
varbinary
,varchar
andchar
is 30 if not specified. - The default precision for
numeric
is 18 if not specified.
- The default precision for
- expression
- is an expression of any type.
- style
- optional argument, of type
int
. It is used by some datatypes to customize the formatting.
Return Types
Returns a value of type datatype.
Remarks
If datatype is varbinary
, varchar
or char
and precision is omitted, it defaults to 30.
If datatype is numeric
and precision is omitted, it defaults to 18.
If expression is NULL, NULL is returned.
Styles are only available for date/time/datetime
converted to/from varchar/char
.
If a style parameter is passed but not used, e.g. convert(int, 10, 123), it is just ignored.
When converting from varchar/char
to date/time/datetime
, if style is omitted, it defaults to 0.
When converting from date/time/datetime
to varchar/char
, if style is omitted, it defaults to 121.
When no style is used, CONVERT(datatype, expression)
is exactly the same as CAST(expression AS datatype)
.
For more control on the format, use the FORMAT
function.
Styles Available for Conversion from date/datetime to varchar
The table below shows all styles available for datetime
, along with the format string used internally and the result.
For date
, you can use the same styles, but the time part is discarded.
The result depends on the current LANGUAGE
setting.
The first line of the table is given by the code sample:
set language fr_FR
declare @d datetime = '2016-03-01 09:05:02.123456789'
print convert(varchar(100), @d, 1)
Style | Standard | Format | Result sample |
---|---|---|---|
1 | US | MM\/dd\/yy |
03/01/16 |
2 | ANSI | yy\.MM\.dd |
16.03.01 |
3 | British/French | dd\/MM\/yy |
01/03/16 |
4 | German | dd\.MM\.yy |
01.03.16 |
5 | Italian | dd\-MM\-yy |
01-03-16 |
6 | - | dd MMM yy |
01 mars 16 |
7 | - | MMM dd\, yy |
mars 01, 16 |
10 | USA | MM\-dd\-yy |
03-01-16 |
11 | Japan | yy\/MM\/dd |
16/03/01 |
12 | ISO | yyMMdd |
160301 |
0, 100 | - | MMM d yyyy h\:mmtt |
mars 1 2016 9:05AM |
101 | US | MM\/dd\/yyyy |
03/01/2016 |
102 | ANSI | yyyy\.MM\.dd |
2016.03.01 |
103 | British/French | dd\/MM\/yyyy |
01/03/2016 |
104 | German | dd\.MM\.yyyy |
01.03.2016 |
105 | Italian | dd\-MM\-yyyy |
01-03-2016 |
106 | - | dd MMM yyyy |
01 mars 2016 |
107 | - | MMM dd\, yyyy |
mars 01, 2016 |
8, 108 | - | HH\:mm\:ss |
09:05:02 |
9, 109 | - | MMM d yyyy h\:mm\:ss\.ffftt |
mars 1 2016 9:05:02.123AM |
110 | USA | MM\-dd\-yyyy |
03-01-2016 |
111 | Japan | yyyy\/MM\/dd |
2016/03/01 |
112 | ISO | yyyyMMdd |
20160301 |
13, 113 | Europe | dd MMM yyyy HH:mm\:ss\.fff |
01 mars 2016 09:05:02.123 |
14, 114 | - | HH:mm\:ss\.fff |
09:05:02.123 |
20, 120 | ODBC | yyyy\-MM\-dd HH\:mm\:ss |
2016-03-01 09:05:02 |
21, 121 | ODBC | yyyy\-MM\-dd HH\:mm\:ss\.fff |
2016-03-01 09:05:02.123 |
126 | ISO8601 | yyyy\-MM\-dd\THH\:mm\:ss\.fff |
2016-03-01T09:05:02.123 |
Styles Available for Conversion from time to varchar
The table below shows all styles available for time
, along with the format string used internally and the result.
They are the same as the styles for datetime
, but the date part is discarded.
The result depends on the current LANGUAGE
setting.
The first line of the table is given by the code sample:
set language fr_FR
declare @t time = '09:05:02.123456789'
print convert(varchar(100), @t, 100)
Style | Standard | Format | Result sample |
---|---|---|---|
0, 100 | - | h\:mmtt |
9:05AM |
8, 108 | - | HH\:mm\:ss |
09:05:02 |
9, 109 | - | h\:mm\:ss\.ffftt |
9:05:02.123AM |
13, 113 | Europe | HH:mm\:ss\.fff |
09:05:02.123 |
14, 114 | - | HH:mm\:ss\.fff |
09:05:02.123 |
20, 120 | ODBC | HH\:mm\:ss |
09:05:02 |
21, 121 | ODBC | HH\:mm\:ss\.fff |
09:05:02.123 |
126 | ISO8601 | HH\:mm\:ss\.fff |
09:05:02.123 |
Styles Available for Conversion from varchar to date/datetime
These styles specify the order of the Day, Month and Year parts when the string is parsed.
Style | Standard | Order of date parts |
---|---|---|
0 | Default | current LANGUAGE |
101 | US | MDY |
102 | ANSI | YMD |
103 | British/French | DMY |
104 | German | DMY |
105 | Italian | DMY |
110 | USA | MDY |
111 | Japan | YMD |
120 | ODBC | YMD |
121 | ODBC | YMD |
Examples
PRINT CONVERT(NUMERIC(12, 2), '8750.4567');
PRINT CAST('8750.4567' AS NUMERIC(12, 2));
The result is:
8750.46
8750.46
Conversion from datetime to varchar:
SET LANGUAGE fr_FR;
DECLARE @d DATETIME = '2016-03-01 09:05:02.15';
PRINT CONVERT(VARCHAR(100), @d, 109);
PRINT CONVERT(VARCHAR(100), @d, 110);
PRINT CONVERT(VARCHAR(100), @d, 120);
The result is:
mars 1 2016 9:05:02.150AM
03-01-2016
2016-03-01 09:05:02
Conversion from varchar to datetime:
SET LANGUAGE en_US;
PRINT '--- en_US, default order for date parts is MDY ---'
PRINT CONVERT(DATETIME, '10-02-1978 13:30'); -- default style is 0
SET LANGUAGE fr_FR;
PRINT '--- fr_FR, default order for date parts is DMY ---'
PRINT CONVERT(DATETIME, '10-02-1978 13:30'); -- default style is 0
SET LANGUAGE en_US;
PRINT '--- en_US, default order for date parts is MDY ---'
PRINT CONVERT(DATETIME, '10-02-1978 13:30', 101); -- style 101 forces parts order to MDY
PRINT CONVERT(DATETIME, '10-02-1978 13:30', 103); -- style 101 forces parts order to DMY
PRINT CONVERT(DATETIME, '10-02-1978 13:30', 102); -- style 101 forces parts order to YMD
The result is:
--- en_US, default order for date part is MDY ---
1978-10-02 13:30:00
--- fr_FR, default order for date part is DMY ---
1978-02-10 13:30:00
--- en_US, default order for date part is MDY ---
1978-10-02 13:30:00
1978-02-10 13:30:00
<Conversion error. VARCHAR CONVERT to DATETIME: wrong date parts order for "10-02-1978 13:30".>