SQL CONVERT Function: Syntax, Usage, and Examples

The SQL CONVERT function changes the data type of a value to another, such as converting a number to a string or a string to a date. It’s especially handy when your query needs specific formatting or data compatibility.

How to Use the SQL CONVERT Function

The general syntax looks like this:

CONVERT(data_type(length), expression, style)

• data_type(length) specifies the target data type.
• expression is the value you're converting.
• style is optional and applies mostly to date and time conversions.

You can convert an integer into a string like this:

SELECT CONVERT(VARCHAR(4), 2025) AS StringYear;

You should use this when displaying numbers in string form, such as formatting years or phone numbers.

To convert a string into a date:

SELECT CONVERT(DATE, '2025-12-31') AS ConvertedDate;

You can rely on this approach when comparing or filtering by date in databases that store dates as strings.

For date-time formatting, the style argument becomes helpful:

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS FormattedDate;

Include the style code if you need to control the format of the resulting string—style 101 formats the date as mm/dd/yyyy.

When to Use the CONVERT Function in SQL

Use the SQL CONVERT function when the data types between columns or values don’t match but still need to be compared or used together. Here are three common use cases:

Formatting Output

Suppose you’re generating a report. You might want to display numbers with fixed lengths or dates in a specific format. CONVERT lets you shape the data to fit presentation needs.

Filtering and Comparison

If a column stores dates as strings but you want to filter based on date ranges, convert the string to an actual DATE type first. This avoids unreliable text-based comparisons.

Type Compatibility for Joins and Unions

Sometimes you're joining tables with mismatched column types. For example, one table might store IDs as INT, another as VARCHAR. Convert one to match the other to make the join work correctly.

Examples of the SQL CONVERT Function

You’ll see CONVERT pop up in real-world SQL queries when formatting data, fixing mismatches, or dealing with legacy tables.

Example: Convert a Number to a String

SELECT CONVERT(VARCHAR(10), 5000) AS Result;

You can use this when appending numbers into a longer formatted string.

Example: Convert String to Date

SELECT CONVERT(DATE, '2024-05-15') AS BirthDate;

This becomes useful when importing CSVs or text-based data where dates are stored as strings.

Example: Convert DateTime to Formatted String

SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Timestamp;

You should use style 120 for a format like yyyy-mm-dd hh:mi:ss when logging timestamps.

Example: Convert Float to Integer

SELECT CONVERT(INT, 15.9) AS RoundedDown;

Use this approach if you need to discard decimals for display or calculation purposes.

Learn More About the SQL CONVERT Function

How SQL CONVERT Differs from CAST

Both CONVERT and CAST change data types, but CONVERT supports style codes and is more flexible for date formatting. CAST follows the SQL standard more closely and works across more databases.

-- Using CAST
SELECT CAST(GETDATE() AS VARCHAR) AS Casted;

-- Using CONVERT
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Converted;

You should prefer CONVERT if you're working with SQL Server and want date styles. Use CAST when writing cross-platform SQL.

Using CONVERT in SQL Server

In SQL Server, the CONVERT function supports many style codes for formatting. These range from 0 (default) to 131 (Hijri date formats), with different codes for European vs. U.S. formats, long vs. short dates, and so on.

SELECT CONVERT(VARCHAR, GETDATE(), 103) AS UKDate;
-- Output: dd/mm/yyyy

You can make your queries more readable and user-friendly by choosing the right format style.

Chaining CONVERT With Other Functions

You might combine CONVERT with functions like ISNULL, LEFT, or DATEADD to build more advanced queries.

SELECT LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10) AS OnlyDate;

You can extract just the date portion of a datetime field this way, without worrying about the time.

Formatting for Export or Reports

If you're exporting data to Excel or generating CSVs, use CONVERT to make sure dates and numbers show up correctly.

SELECT
  CONVERT(VARCHAR, OrderDate, 101) AS OrderDateFormatted,
  CONVERT(VARCHAR, Total, 1) AS TotalFormatted
FROM Orders;

Here, style 1 for the number gives you commas as thousand separators (e.g., 1,234.56), while style 101 formats the date for U.S. audiences.

Using CONVERT in WHERE Clauses

Filtering a datetime column based on just the date? Convert both sides to DATE:

SELECT * FROM Events
WHERE CONVERT(DATE, EventTime) = '2024-12-25';

You can use this to ignore the time portion entirely and match just the day.

Handling NULLs With CONVERT

The CONVERT function returns NULL if the input can’t be converted to the desired type. That means no error, but you might lose rows unexpectedly.

SELECT CONVERT(INT, 'abc') AS Result;
-- Returns NULL

You should handle this by using ISNULL or TRY_CONVERT (in SQL Server 2012+):

SELECT TRY_CONVERT(INT, 'abc') AS SafeResult;

TRY_CONVERT gives you a safer way to handle potential conversion failures without crashing your query.

Using the SQL CONVERT function gives you precision and control over how data appears and interacts in your queries. Whether you’re formatting a report, joining tables, or cleaning imported data, CONVERT helps you shape the results into exactly what you need. Be mindful of style codes for date formats and always consider whether a conversion might silently return NULL if it fails.