SQL Convert Function And SQL Date Format Function

Summary: In this tutorial, you will learn how to use them SQL CONVERT Function to convert a value of one type to another also, we will review SQL Server data type converting operations and TRY_CONVERT build-in-functions with the various sample. therefore, first of all, clarify and explain the syntax of the CONVERT function and then we will discuss how can we make data converting process numerical and date/time data types to character data.

SQL Server CONVERT() function Overview

In SQL Server (T-SQL), the CONVERT SQL function converts an expression from one data type to another data type. if the conversion fails, then the function will return an error. Otherwise, they return the converted value.
The CONVERT() function in SQL server allows you to convert a value of one type to another.

Syntax:

The syntax for the SQL CONVERT function is:

Parameters Value or Arguments

Value	Description
data_type	Required. The datatype that you wish to convert an expression to. It can be one of the following: int, bigint, smallint, tinyint, bit, numeric, decimal, money, smallmoney, real, float, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length)	Optional. The length of the resulting data type such as for char, varchar, nchar, nvarchar, binary, and varbinary.
expression	Required. The value to convert to another data-type
style	Optional. The format used to convert between data-types, such as a date format or string format. can be one of the following values:

The CONVERT() Function in SQL Server returns the value of expression translated to the data_type with a specified style.
Therefore, the CONVERT() is similar to the CAST() function. However, it is specific to SQL Server. In contrast, the CAST() Function in SQL Server is a part of ANSI-SQL functions, which is widely available in many other database products.

Converting Datetime To Character:

Value (without century)	Value (with century)	Input/Output	Standard
0	100	mon dd yyyy hh:miAM/PM	Default
1	101	mm/dd/yyyy	US
2	102	yyyy.mm.dd	ANSI
3	103	dd/mm/yyyy	British/French
4	104	dd.mm.yyyy	German
5	105	dd-mm-yyyy	Italian
6	106	dd mon yyyy	–
7	107	Mon dd, yyyy	–
8	108	hh:mm:ss	–
9	109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	mm-dd-yyyy	USA
11	111	yyyy/mm/dd	Japan
12	112	yyyymmdd	ISO
13	113	dd mon yyyy hh:mi:ss:mmm	Europe (24-hour clock)
14	114	hh:mi:ss:mmm	24-hour clock
20	120	yyyy-mm-dd hh:mi:ss	ODBC canonical (24-hour clock)
21	121	yyyy-mm-dd hh:mi:ss.mmm	ODBC canonical (24-hour clock)
	126	yyyy-mm- ddThh:mi:ss.mmm	ISO8601
	127	yyyy-mm- ddThh:mi:ss.mmmZ	ISO8601 (with time zone Z)
	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

Converting Float To Real:

Value	Explanation
0	Maximum 6 digits (default)
1	8 digits
2	16 digits

Converting Money To Character

Value	Explanation
0	No comma delimiters, 2 digits to the right of the decimal (ie: 2345.67)
1	Comma delimiters, 2 digits to the right of the decimal (ie: 2,345.67)
2	No comma delimiters, 4 digits to the right of the decimal (ie: 2345.6789)

The CONVERT function in SQL Server used in the following versions of SQL Server (T-SQL):
SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

SQL Server CONVERT() function examples:

Let see the following examples of using the CONVERT() function.

I) Using the SQL CONVERT() function to convert a decimal to an integer

This example uses the CONVERT() function to convert the decimal number 16.75 to an integer:

Output:

II) Using the CONVERT() function to convert a decimal to another decimal with different length

Therefore, In this example, we used the CONVERT() function to convert the decimal number 16.75 to another decimal number with zero scales:

Output:

III) Using the CONVERT() function to convert a string to a DateTime value

Therefore, In this SQL Datetime formate example, uses the CONVERT() function to convert the string ‘2015-07-21’ to a DateTime value:

Output:

IV) Using the CONVERT() function to convert a DateTime value to a string value

In this an example used the CONVERT() function to convert the current date and time to a string with a specific style:

Output:

SQL Server TRY_CONVERT() Function

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

Output:

Furthermore, on the other hand, suppose, if we used the SQL TRY_CONVERT function instead of the SQL CONVERT function for the same query then, it will return the NULL value.

Output:

Conclusion:

In this SQL Convert() Function, you have learned how to use the CONVERT() function in SQL Server to convert a value from one type to another with an example, and also I recommend that you be careful with date/time data type conversion. I hope you will enjoy it!