The Purpose of CONVERT function in SQL

While entering or manipulating data in SQL databases, you may require converting data from one type to another. For example, converting a user entered date as string into the datetime data type before inserting into the table.

Similarly, converting a string to int, date to datetime, a floating number to int and vice versa.

The MS SQL CONVERT function enables us changing the data type of expression from one to another. For example:

SELECT CONVERT(varchar, 50.55);

SELECT CONVERT(int, 101.55);

SELECT (decimal(6,4), 60.55);

The arguments required in CONVERT SQL function

The general syntax of using the CONVERT function is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The CONVERT function requires two arguments:

• The target data type that you want to convert the expression. You may optionally provide the length e.g. varchar(100), decimal (6,4) etc.
• The expression. This is the value that is required to be converted into the targeted data type.

An example of CONVERT function

The query below shows converting a string into an int value by using the CONVERT function. Basically, the string is a number that is enclosed in single quotes. Have a look:

Query:

SELECT CONVERT(INT, ’22’) AS “String to Int”;

Result:

The example of float to int by CONVERT function

This example converts a floating number to an int type.

Query:

SELECT CONVERT(INT, 22.78)

AS "Float to Int";

The output:

From the output, it can be seen that float to int conversion simply truncated the number after the decimal point. It did not round the number. If it was rounded, the result should have been 23; so be careful, as you convert floating or other numbers to int type by CONVERT function.

Convert a string to date example

In the following query, the given string is converted into datetime data type.  See the queries with different formats and their output:

Queries:

SELECT CONVERT(datetime, 'Aug 20 2017') AS "String to date";



SELECT CONVERT(datetime, '2017-08-20') AS "String to date";



SELECT CONVERT(datetime, '08/20/2017') AS "String to date";

The result:

All the date formats as string resulted in the same output.

Converting datetime to date example using table data

You may also convert a datetime type to date by using CONVERT function. For demonstrating that, I am going to use table data.

In the query, I fetched the rows from sto_employees table that stores joining date of the employees along with other information. The join_date column is datetime type; so it stores date + time.

The query uses CONVERT function where datetime is converted to date as follows:

SELECT emp_name, join_date, CONVERT(date, join_date) AS "Just date"

FROM sto_employees;

The resultset:

You can see the datetime column is displayed parallel to date column for showing the difference.

The numeric to int conversion using table data

Our above example table also stores employee salaries in the emp_salary column for the demo only. The datatype of the emp_salary column is numeric(8,2), so it may store salaries with the decimal point.

By using CONVERT function, we will convert all records in the table for emp_salary column into the int type. For showing the difference, the original values are also displayed:

The numeric to int query:

SELECT emp_name, emp_salary, CONVERT(int, emp_salary) AS "numeric to int"

FROM sto_employees;

The resultset:

Again, you can see the salary numbers are truncated rather than rounded off.

Converting datetime to string example

Just like string to datetime and date conversions, you may convert a datetime value to the string type e.g. varchar.

Using the join_date column in our example table, this query converts datetime column to varchar(60) and displayed the more readable date as follows:

SELECT emp_name, emp_salary, join_date,

CONVERT(varchar(60), join_date) AS "datetime to String"

FROM sto_employees;

The result:

For learning more about date and time styles, you may refer to this document in Microsoft website.