Esat Erkec

SQL Convert Function

January 29, 2019 by

In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data.

Implicit vs Explicit

The process of changing data type of a value into another data type is referred to as data type conversion and also almost all programing languages include some type of data converting functions or functionality. When we turn our perspective to SQL Server in order to discuss details of SQL data converting operations, at first we can separate data conversion process into two parts; implicit and explicit conversions. Implicit conversion is done by SQL Server for internal needs and you can also find additional details in the article Implicit conversion in SQL Server. Explicit conversion is performed explicitly by a database programmer or administrator and at the same time this conversion process is made with help of any data conversion function. In this article, we will particularly focus on the SQL CONVERT function. This function provides a means to convert one data type to another specified data type. At first, we will interpret the syntax of the SQL CONVERT function.

Syntax

data_type: This parameter defines the target data type which is to be converted. data_type parameter can take these data types as an input which are shown in the below array list.

“bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image”

length: This is an optional parameter which specifies the target data type length. The default value of this parameter is 30.

expression: This parameter specifies the value which we want to convert to another data type.

style: This is an integer parameter which specifies the output style of the converted value. This value is more useful to date data type formats.

Now, we will reinforce this theoretical instructions with various practical samples.

Convert Float to Int

In this example, we will convert a float data type to integer. In the following query, we will declare a variable that data type is float and then we will use the SQL CONVERT function in order to convert float value to integer so for that we will perform data converting operation.

Convert Float to Varchar

In this example, we will convert a float value to varchar value. This example is very similar to previous one but the only difference is that we will convert float value to varchar.

In the following example, we will not send any value to length parameter of the SQL CONVERT function so length parameter must be set to default value. As already we noted in the syntax explanation of the SQL CONVERT function, this default value is 30 and now we will prove this. In the following query, we will create a temporary table and we will insert converted value to this table and then we will analyze the data structure of this table.

As you can see in the above image, the float data type was converted to varchar value and we did not set the length parameter so SQL Server applied the default value of the length parameter as 30.

Now, we will set the length parameter of the SQL CONVERT function and then recheck the length of the varchar data type.

Convert Money to Varchar

The SQL Server money data type helps to store monetary values. We can define monetary values preceded by a currency symbol however SQL Server does not store the currency symbols or any data which is related to currency symbol. In the following query, we will convert the money data type to varchar and we will also use style parameter of the SQL convert function. With the help of the style parameter, we can determine comma delimiters and length of the right decimal digit. The following chart shows the style parameter value and output of the SQL CONVERT function.

Value

Style Parameter

Comma Delimiter

Right decimal digit

Output

$4936.56

0

no

2

4936.56

$4936.56

1

yes

2

4,936.56

$4936.56

2

no

4

4936.5600

Convert Float and Real to Varchar

Float and real data types are approximate numeric data types in SQL Server and it means that these data types do not store exact values. They store a highly close approximation of the stored value. Now, we will explain the approximate numeric data type conception. In the following query, we will create a table which has two columns and these column’s data types are float and real and then we will insert some numeric values. Actually, we expected two digit numbers in the table however real and float data type stores the closest values to expected values.

The below table is quoted from Microsoft Docs and this table explains the converting style of the float and real data types to textual data.

Tip: Scientific notation is a special method in expressing very large number or small numbers mostly used in science.

Value

Output

0 (default)

A maximum of 6 digits. Used in scientific notation, when appropriate.

1

Always 8 digits. Always used in scientific notation.

2

Always 16 digits. Always used in scientific notation.

3

Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string.

Now let’s demonstrate an example which includes all data conversion styles according to this table.

As you can see in the above, the result set shows that the style parameter changes the output of the SQL CONVERT function.

Convert Date/Time to Varchar


Date/Time formats can vary according to locale settings and for this reason we require various formats to represent date/time. Imagine that you have an application and different countries users use this application, so if someone connects this application in U.S, you have to represent the date as month/day/year format. On the other side, another user connects this application in U.K. and you have to represent date format as day/month/year format. In this case we have to determine the date/time representing style according to locale date/time setting. sp_helplanguage stored procedure returns supported languages with detailed information’s in SQL Server.

In addition, we can determine the language for stated session and in this way we can adapt date/time functions to particular language. In the following query, we will set language to Polish and then we can see the changing of the month and day name according to Polish locale.

Tip: As general rule, if you will design a multinational application database, always store the UTC (Coordinated Universal Time) time in one column so that you can avoid date/time problems due to local settings.

The SQL Server CONVERT function offers several options to convert date/time data type to character data and also this character data output can be styled in different standards through the style parameter, such as if we want to convert a GETDATE built-in function result to the German standard, we can use the following query:

In the following cheat table, you can find each usage combination of style parameter and whole output format of SQL CONVERT () function for date/time to character data conversions.

Standard

Style

Output

Century

Default

0

mon dd yyyy hh:miAM/PM

U.S.A.

1

mm/dd/yy

 

ANSI

2

yy.mm.dd

 

British/French

3

dd/mm/yy

 

German

4

dd.mm.yy

 

Italian

5

dd-mm-yy

 

Shortened month name

6

dd mon yy

 

Shortened month name

7

mon dd,yy

 

24 hour time

8

hh:mm:ss

 

Default + milliseconds

9

mon dd yyyy hh:mi:ss:mmmAM/PM

USA

10

mm-dd-yy

 

JAPAN

11

yy/mm/dd

 

ISO

12

yymmdd

 

Europe default + milliseconds

13

dd mon yyyy hh:mi:ss:mmm

 24 hour time with milliseconds

14

hh:mi:ss:mmm

 

ODBC canonical

20

yyyy-mm-dd hh:mi:ss

ODBC canonical (with milliseconds)

21

yyyy-mm-dd hh:mi:ss.mmm

Default

100

mon dd yyyy hh:miAM/PM

U.S.

101

mm/dd/yyyy

ANSI

102

yyyy.mm.dd

British/French

103

dd/mm/yyyy

German

104

dd.mm.yyyy

Italian

105

dd-mm-yyyy

Shortened month name

106

dd mon yyyy

Shortened month name

107

mon dd, yyyy

 24 hour time

108

hh:mm:ss

 

Default + milliseconds

109

mon dd yyyy hh:mi:ss:mmmAM/PM

USA

110

mm-dd-yyyy

JAPAN

111

yyyy/mm/dd

ISO

112

yyyymmdd

Europe default + milliseconds

113

dd mon yyyy hh:mi:ss:mmm

 24 hour time with milliseconds

114

hh:mi:ss:mmm

 

ODBC canonical

120

yyyy-mm-dd hh:mi:ss

ODBC canonical (with milliseconds)

121

yyyy-mm-dd hh:mi:ss.mmm

ISO8601

126

yyyy-mm-ddThh:mi:ss.mmm

ISO8601 with time zone Z.

127

yyyy-mm-ddThh:mi:ss.mmm

Hijri

130

dd mon yyyy hh:mi:ss:mmmAM/PM

(Hijri)

Hijri

131

dd/mm/yy hh:mi:ss:mmmAM/PM

(Hijri)

ow, we will demonstrate this cheat table with all combinations for the SQL GETDATE function so that we can figure out the influence of the style parameter to the SQL CONVERT function outputs.

Now, we will mention some points which we have to consider about date/time conversion operation. At the beginning of this section, we talked about SQL Server language options and settings, so if we change the language setting it affects some of the date/time conversion style outputs. In the bellow example, we want to convert results from the GETDATE function so this setting directly affects the abbreviated month name of the SQL CONVERT function.

Another consideration is about year format. In some circumstances, we can use style parameter which does not return century format but this case may cause some confusing situations. Such as in the following sample we have two different dates and also these dates store different years however the output of the SQL CONVERT function are similar.

TRY_CONVERT ()

The SQL TRY_CONVERT function is an advanced form of the SQL CONVERT function. The main advantage of the SQL TRY_CONVERT function is protecting from data converting errors during query execution. It is possible that we can experience errors on data convert process with the SQL COVERT operation due to non-suitable or dirty data. However, the SQL TRY_CONVERT function allows us to avoid these types of errors. At the same time, there are no syntax differences between the SQL CONVERT and TRY_CONVERT functions. The SQL TRY_CONVERT function returns NULL value if the data conversion generate an error. In the below example, at first we will try to convert character value to an integer through the SQL CONVERT function and this operation will return an error.

On the other hand, if we use the SQL TRY_CONVERT function instead of the SQL CONVERT function for the same query it will return NULL value.

Conclusion

In this article, we thoroughly reviewed the SQL CONVERT function syntax, details and significant considerations. Data conversion processes have wide usage, in practice, therefore in this article we demonstrated various examples. I am recommend that you be careful with date/time data type conversion if your database is being used in different countries. Likewise formatting to date/time data types is another important point.

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec
Esat Erkec
1,094 Views