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 and char is 30 if not specified.
  • The default precision for numeric is 18 if not specified.
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".>
RSQL, a simple alternative to Microsoft SQL Server