SQL Server CONVERT Function: How to Convert Data Types in SQL Server

As a SQL Server developer, you may encounter situations where you must convert data types from one form to another. The SQL Server CONVERT function can help you achieve this task by changing the data type of an expression to another data type.

This article will cover everything you need to know about the SQL Server CONVERT function. I’ll explain the function and how to use it and provide code examples in SQL Server. We’ll also compare the CONVERT function to similar functions in SQL Server, like CAST, FORMAT, and PARSE.

What is a CONVERT function in SQL Server?

The CONVERT function in SQL Server changes the data type of an expression. The function takes three arguments: the target data type, the expression to be converted, and an optional style argument.

The target data type can be any valid SQL Server data type, including numeric, character, and date/time data types. The expression to be converted can be any valid expression that returns a value. The style argument is optional and specifies the formatting style used when converting data types.

How to Use SQL Server CONVERT Function

The syntax for the SQL Server CONVERT function is as follows:

Syntax

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

Let’s break down the syntax:

• data_type: This is the target data type you want to convert the expression. It can be any valid SQL Server data type, including numeric, character, and date/time data types.
• length: This argument is optional and specifies the length of the target data type. For example, if the target data type is varchar, you can specify the maximum length of the string.
• expression: This is the expression you want to convert to the target data type. It can be any valid expression that returns a value.
• style: This argument is optional and specifies the formatting style used when converting data types.

Implicit and explicit data type conversion

In SQL Server, there are two types of data type conversions: implicit and explicit. Implicit conversion happens automatically when SQL Server converts one data type to another without being told to do so. Explicit conversion occurs when you use a function to convert data types explicitly.

Explicit conversion is necessary when changing the data type of a column or variable. The CONVERT function is one of the most commonly used functions for explicit data type conversion in SQL Server.

SQL CONVERT function example

Here are some examples of how to use the CONVERT function in SQL Server.

Example 1: Convert date and time data types

In this example, we’ll convert a date data type to a varchar data type using the CONVERT function. We’ll also specify the formatting style when converting the data type.

SELECT CONVERT(varchar(10), GETDATE(), 101) AS [Date]

Output:

Date
-----------
02/17/2023

We used the GETDATE() function in this example to get the current date and time. We then used the CONVERT function to convert the date to a varchar data type. We specified the formatting style 101, representing the format mm/dd/yyyy.

Example 2: Convert numeric data types

In this example, we’ll convert a numeric data type to a varchar data type using the CONVERT function. We’ll also specify the formatting style when converting the data type.

SELECT CONVERT(varchar(10), 12345.67, 1) AS [NumericToVarChar]

Output:

NumericToVarChar
-----------
12345.67

In this example, we used the CONVERT function to convert a numeric data type to a varchar data type. We specified the formatting style 1, which represents the format with commas and two decimal places.

Example 3: Convert money data types

In this example, we’ll convert a money data type to a varchar data type using the CONVERT function. We’ll also specify the formatting style when converting the data type.

SELECT CONVERT(varchar(10), 12345.67, 2) AS [Money]

Output:

Money
-----------
12345.67

In this example, we used the CONVERT function to convert a money data type to a varchar data type. We specified the formatting style 2, representing the format with commas and two decimal places.

Using CONVERT Function with the GetDate() Function

You can also use the CONVERT function with the GetDate() function in the SQL Server statement.
Here we are using CONVERT() function to modify the Date that has been provided in different formats.
Here is an example:

SELECT 
    GetDate() AS CurrentDate, 
    CONVERT(varchar, GetDate(), 101) AS DateStyle101, 
    CONVERT(varchar, GetDate(), 102) AS DateStyle102,
    CONVERT(varchar, GetDate(), 103) AS DateStyle103, 
    CONVERT(varchar, GetDate(), 104) AS DateStyle104, 
    CONVERT(varchar, GetDate(), 105) AS DateStyle105,
    CONVERT(varchar, GetDate(), 106) AS DateStyle106, 
    CONVERT(varchar, GetDate(), 107) AS DateStyle107, 
    CONVERT(varchar, GetDate(), 108) AS DateStyle108,
    CONVERT(varchar, GetDate(), 109) AS DateStyle109, 
    CONVERT(varchar, GetDate(), 110) AS DateStyle110, 
    CONVERT(varchar, GetDate(), 111) AS DateStyle111,
    CONVERT(varchar, GetDate(), 112) AS DateStyle112, 
    CONVERT(varchar, GetDate(), 113) AS DateStyle113, 
    CONVERT(varchar, GetDate(), 114) AS DateStyle114,
    CONVERT(varchar, GetDate(), 120) AS DateStyle120, 
    CONVERT(varchar, GetDate(), 121) AS DateStyle121, 
    CONVERT(varchar, GetDate(), 126) AS DateStyle126,
    CONVERT(varchar, GetDate(), 127) AS DateStyle127;

Output:

The following is the cheat table that includes the standard date/time format for each style code:

Alternatives to the CONVERT function

While the CONVERT function is the most commonly used function for explicit data type conversion in SQL Server, there are other functions you can use as well. Let’s take a look at some of them.

01. CAST function in SQL Server

The CAST function in SQL Server is another function you can use to convert data types. It works similarly to the CONVERT function but has a slightly different syntax.

Here is an example:

SELECT CAST('12345.67' AS money) AS [Money]

In this example, we used the CAST function to convert a varchar data type to a money data type.

02. FORMAT function in SQL Server

The FORMAT function in SQL Server is used to format data types in a specific way. We can use it to format numeric, date/time, and string data types.
Here is an example:

SELECT FORMAT(12345.67, 'C') AS [Currency]

We used the FORMAT function in this example to format a numeric data type as a currency.

03. PARSE function in SQL Server

The PARSE function in SQL Server converts a string to a specific data type. It’s useful when converting a string to a particular data type, but the string format may vary.
Here is an example:

SELECT PARSE('05/01/2023' AS date USING 'en-US') AS [Date]

In this example, we used the PARSE function to convert a string to a date data type. We specified the format of the string using the ‘en-US’ parameter.

FAQs

Reference: W3School-SQL Server Convert Function()

You might want to read this too:

• Having VS Where Clause in SQL
• Stored Procedure in SQL Server – A Complete Guide [with Examples]
• SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]
• Function vs Stored procedure in SQL Server
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples
• SQL pivot tables
• Different ways to delete duplicate rows in SQL Server
• UNION vs UNION ALL in SQL SERVER
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Group by Month in SQL
• Types of Joins in SQL Server

Let others know about this post by sharing it and leaving your thoughts in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024