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
|
1 |
CONVERT (datatype (length), expression, style) |
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.
|
1 2 3 4 5 6 |
--Declaring a float variable DECLARE @FloatVal AS Float --Assign a float value to variable SET @FloatVal = 1132.12345 --Convert float value to int SELECT CONVERT(int, @FloatVal) AS ConvertedValue |
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.
|
1 2 3 4 5 6 |
--Declaring a float variable DECLARE @FloatVal AS Float --Assign a float value to variable SET @FloatVal = 1132.12345 --Convert float value to varchar SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue |
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.
|
1 2 3 4 5 6 7 8 9 10 |
DROP TABLE IF EXISTS TestConvertedTable --Declaring a float variable DECLARE @FloatVal AS Float --Assign a float value to variable SET @FloatVal = 1132.12345 --Convert float value to varchar SELECT CONVERT(varchar, @FloatVal) AS ConvertedValue INTO TestConvertedTable --- Analyze the temporary table SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable' |
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.
|
1 2 3 4 5 6 7 8 9 10 |
DROP TABLE IF EXISTS TestConvertedTable --Declaring a float variable DECLARE @FloatVal AS Float --Assign a float value to variable SET @FloatVal = 1132.12345 --Convert float value to varchar SELECT CONVERT(varchar(20), @FloatVal) AS ConvertedValue INTO TestConvertedTable --- Analyze the temporary table SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TestConvertedTable' |
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 |
|
1 2 3 4 5 |
DECLARE @Money AS MONEY=$4936.56 SELECT CONVERT(Varchar,@money,0) AS [Style_0] , CONVERT(Varchar,@money,1) AS [Style_1] , CONVERT(Varchar,@money,2) AS [Style_2] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP TABLE IF EXISTS TestNumericType CREATE TABLE TestNumericType (TestValFloat FLOAT,TestValReal REAL) DECLARE @ValueFloat FLOAT =0.1 DECLARE @ValueReal REAL =0.1 DECLARE @i INT=0 WHILE @i <=1000 BEGIN SET @ValueFloat=@ValueFloat + 0.1 SET @ValueReal = @ValueReal + 0.1 INSERT INTO TestNumericType (TestValFloat,TestValReal) VALUES(@ValueFloat,@ValueReal) SET @i = @İ+1 END SELECT * FROM TestNumericType |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @VarFl float=11234561231231.234 SELECT CONVERT(varchar(100),@VarFl,0) AS [Style_0], CONVERT(varchar(100),@VarFl,1) AS [Style_1], CONVERT(varchar(100),@VarFl,2) AS [Style_2], CONVERT(varchar(100),@VarFl,3) AS [Style_3], STR(CONVERT(varchar(100),@VarFl,0),20,16) as [6_Digit], STR(CONVERT(varchar(100),@VarFl,1),20,16) as [8_Digit], STR(CONVERT(varchar(100),@VarFl,2),20,16) as [16_Digit], STR(CONVERT(varchar(100),@VarFl,3),20,16) as [17_Digit] |
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.
|
1 2 3 |
SET LANGUAGE 'Polish' SELECT DATENAME(month, DATEPART(MONTH,GETDATE())) AS [MonthName_Polish] , DATENAME(WEEKDAY, DATEPART(WEEKDAY,GETDATE())) AS [DayName_Polish] |
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:
|
1 |
SELECT GETDATE() AS [NotFormatedDate],CONVERT(Varchar(50),GETDATE(),104) AS [GermanStyleDate] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
SELECT 'Default ' AS [Standart] ,'0' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),0) AS [ConvertedFormat] UNION ALL SELECT 'U.S.A.' AS [Standart] ,'1' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),1) AS [ConvertedFormat] UNION ALL SELECT 'ANSI' AS [Standart] ,'2' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),2) AS [ConvertedFormat] UNION ALL SELECT 'British/French' AS [Standart] ,'3' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),3) AS [ConvertedFormat] UNION ALL SELECT 'German' AS [Standart] ,'4' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),4) AS [ConvertedFormat] UNION ALL SELECT 'Italian' AS [Standart] ,'5' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),5) AS [ConvertedFormat] UNION ALL SELECT 'Shortened month name' AS [Standart] ,'6' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),6) AS [ConvertedFormat] UNION ALL SELECT 'Shortened month name' AS [Standart] ,'7' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),7) AS [ConvertedFormat] UNION ALL SELECT '24 hour time' AS [Standart] ,'8' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),8) AS [ConvertedFormat] UNION ALL SELECT 'Default + milliseconds' AS [Standart] ,'9' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),9) AS [ConvertedFormat] UNION ALL SELECT 'USA' AS [Standart] ,'10' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),10) AS [ConvertedFormat] UNION ALL SELECT 'JAPAN' AS [Standart] ,'11' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),11) AS [ConvertedFormat] UNION ALL SELECT 'ISO' AS [Standart] ,'12' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),12) AS [ConvertedFormat] UNION ALL SELECT 'Europe default + milliseconds' AS [Standart] ,'13' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),13) AS [ConvertedFormat] UNION ALL SELECT ' 24 hour time with milliseconds' AS [Standart] ,'14' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),14) AS [ConvertedFormat] UNION ALL SELECT 'ODBC canonical' AS [Standart] ,'20' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),20) AS [ConvertedFormat] UNION ALL SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'21' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),21) AS [ConvertedFormat] UNION ALL SELECT 'Default ' AS [Standart] ,'100' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),100) AS [ConvertedFormat] UNION ALL SELECT 'U.S.' AS [Standart] ,'101' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),101) AS [ConvertedFormat] UNION ALL SELECT 'ANSI' AS [Standart] ,'102' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),102) AS [ConvertedFormat] UNION ALL SELECT 'British/French' AS [Standart] ,'103' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),103) AS [ConvertedFormat] UNION ALL SELECT 'German' AS [Standart] ,'104' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),104) AS [ConvertedFormat] UNION ALL SELECT 'Italian' AS [Standart] ,'105' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),105) AS [ConvertedFormat] UNION ALL SELECT 'Shortened month name' AS [Standart] ,'106' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),106) AS [ConvertedFormat] UNION ALL SELECT 'Shortened month name' AS [Standart] ,'107' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),107) AS [ConvertedFormat] UNION ALL SELECT ' 24 hour time' AS [Standart] ,'108' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),108) AS [ConvertedFormat] UNION ALL SELECT 'Default + milliseconds' AS [Standart] ,'109' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),109) AS [ConvertedFormat] UNION ALL SELECT 'USA' AS [Standart] ,'110' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),110) AS [ConvertedFormat] UNION ALL SELECT 'JAPAN' AS [Standart] ,'111' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),111) AS [ConvertedFormat] UNION ALL SELECT 'ISO' AS [Standart] ,'112' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),112) AS [ConvertedFormat] UNION ALL SELECT 'Europe default + milliseconds' AS [Standart] ,'113' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),113) AS [ConvertedFormat] UNION ALL SELECT ' 24 hour time with milliseconds' AS [Standart] ,'114' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),114) AS [ConvertedFormat] UNION ALL SELECT 'ODBC canonical' AS [Standart] ,'120' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),120) AS [ConvertedFormat] UNION ALL SELECT 'ODBC canonical (with milliseconds)' AS [Standart] ,'121' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),121) AS [ConvertedFormat] UNION ALL SELECT 'ISO8601' AS [Standart] ,'126' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),126) AS [ConvertedFormat] UNION ALL SELECT 'ISO8601 with time zone Z.' AS [Standart] ,'127' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),127) AS [ConvertedFormat] UNION ALL SELECT 'Hijri' AS [Standart] ,'130' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),130) AS [ConvertedFormat] UNION ALL SELECT 'Hijri' AS [Standart] ,'131' AS [Style] ,CONVERT(VARCHAR(100),GETDATE(),131) AS [ConvertedFormat] |
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.
|
1 2 3 4 5 6 7 8 9 10 |
SET LANGUAGE lietuvių; SELECT DATEADD(YEAR,-100,GETDATE()) AS [Year_1919] ,CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate] SET LANGUAGE Croatian; SELECT DATEADD(YEAR,100,GETDATE()) AS [Year_2119], CONVERT(varchar(100),GETDATE(),0) AS [ConvertedDate] |
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.
|
1 2 3 4 5 6 7 8 |
SELECT DATEADD(YEAR,-100,GETDATE()) AS [Year_1919] ,CONVERT(varchar(100),DATEADD(YEAR,-100,GETDATE()),1) AS [ConvertedDate] SELECT DATEADD(YEAR,100,GETDATE()) AS [Year_2119], CONVERT(varchar(100),DATEADD(YEAR,100,GETDATE()),1) AS [ConvertedDate] |
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.
|
1 |
SELECT CONVERT(INT,'AnyString') AS ConvertFunc |
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.
|
1 |
SELECT TRY_CONVERT(INT,'AnyString') AS ConvertFunc |
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.
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023