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 a string into the datetime data type before inserting it into the table.

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

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:

The CONVERT function requires two arguments:

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:

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 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 strings resulted in the same output.

Converting datetime to date example using table data

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

In the query, I fetched the rows from the 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 the 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 the date column to show 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.

To show 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 displays 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: