SQL convert: Changing Data Formats

Photo of author
Jake Colins

In the world of databases, managing data isn’t just about storing and retrieving it — it also involves manipulating and transforming that data into a format that’s meaningful, consistent, and usable. Whether you’re building reports, creating APIs, or just cleaning up after messy imports, understanding how to convert data formats in SQL is a powerful tool every data professional should master.

TL;DR

SQL provides built-in functions like CAST and CONVERT that allow you to change data types to suit your application’s needs. These conversions are useful when dealing with dates, numbers, and strings, especially when transitioning between different systems or preparing clean reports. Understanding implicit vs. explicit conversions is crucial to avoid errors. Mastering type conversion makes your SQL queries more robust, cleaner, and widely compatible.

Why Convert Data Types in SQL?

Imagine you have a database with product prices stored as strings. You’ll find it incredibly challenging to perform calculations, sorting, or any kind of analytics without first converting that data into a numeric type. Similarly, formatting a date stored as text into a real date helps streamline temporal queries and reporting.

Here are some of the typical scenarios when you’d want to convert data types in SQL:

  • When importing data from external sources like CSVs or Excel files
  • When cleaning data from inconsistent inputs
  • When normalizing schemas for performance optimization
  • When preparing data for presentation layers (like reports or dashboards)

Implicit vs. Explicit Conversion

SQL engines can sometimes automatically convert data — this is known as implicit conversion. For example, comparing a string to an integer might work because SQL quietly converts one to match the other in the background. However, this can lead to unexpected errors or inefficiencies.

On the other hand, explicit conversion happens when you specifically instruct SQL to change a data type using conversion functions. It’s a safer and clearer approach, especially in large codebases or production environments.

The CAST() Function

CAST is part of the ANSI SQL standard and is supported in most relational databases, including SQL Server, PostgreSQL, MySQL, and Oracle.

Syntax:

CAST(expression AS target_data_type)

Examples:

  • CAST('2024-01-01' AS DATE) → Converts a string into a date format.
  • CAST(100.75 AS INT) → Converts a decimal number into an integer (result: 100).

The primary benefit of CAST is its portability across different types of SQL systems.

The CONVERT() Function

The CONVERT function is a bit more powerful than CAST, but it’s specific to SQL Server (and some other RDBMSs). It allows greater control, especially when formatting date and time values.

Syntax:

CONVERT(target_data_type, expression [, style])

Examples:

  • CONVERT(INT, '12345') → Converts a string to an integer.
  • CONVERT(VARCHAR, GETDATE(), 103) → Converts today’s date into British format (dd/mm/yyyy).

The optional “style” parameter is particularly helpful when working with regional date formats. SQL Server provides a range of style codes that control the output format of datetime conversions.

Converting Between Common Data Types

1. String to Number

This is a frequent requirement, especially when ingesting data from CSVs and external resources where numbers are often enclosed in quotes. Use:

CAST('123.45' AS FLOAT)

or

CONVERT(NUMERIC(10,2), '123.45')

2. Number to String

This is useful for display purposes:

CAST(300 AS VARCHAR)

3. String to Date

Trickier, as the source string must match a recognizable date format. Use:

CAST('2024-06-10' AS DATE)

or, if using SQL Server with formatting:

CONVERT(DATETIME, '10/06/2024', 103)

4. Date to String

CAST(GETDATE() AS VARCHAR)

This is often used in logging or displaying information in UIs.

Handling Complex Scenarios

There are situations where data might not be in a clean format. For example, you may receive corrupted strings or numbers with commas (e.g., “1,200.50”). Attempting to convert without cleaning them first will result in errors.

Tip: Use functions like REPLACE() to pre-process values:

CAST(REPLACE('1,200.50', ',', '') AS DECIMAL(10, 2))

Always validate data before converting. Wrapping conversions in a TRY_CAST() or TRY_CONVERT() function (SQL Server) or using CASE statements to check type with regular expressions can avoid serious runtime errors.

Error Handling

In SQL Server, you can use the safer alternatives:

  • TRY_CAST()
  • TRY_CONVERT()

These return NULL instead of throwing an error when a conversion fails, making them perfect for volatile datasets.

TRY_CAST('abc' AS INT)

The above will return NULL instead of breaking your query.

Performance Matters

While casting and converting are powerful, they can also be CPU-intensive when applied across millions of rows. Furthermore, using conversions in WHERE clauses on indexed columns can prevent SQL from using the index, severely degrading performance.

Instead of this:

WHERE CAST(created_at AS DATE) = '2024-06-10'

Try this:

WHERE created_at >= '2024-06-10' AND created_at < '2024-06-11'

This approach lets SQL optimize better without type conversions on indexed columns.

Use Cases Across Different Database Systems

Almost all databases support type conversion, but syntax and features vary:

  • PostgreSQL: Use :: syntax — '123'::int
  • MySQL: Offers CAST() and uses STR_TO_DATE() for date conversions
  • Oracle: Heavily uses TO_CHAR() and TO_DATE()
  • SQL Server: Offers CAST(), CONVERT(), TRY_CAST(), etc.

If you’re writing cross-platform SQL, prefer using CAST for better compatibility, and leave database-specific formatting to the presentation layer if possible.

Best Practices

Here are some tips to keep in mind:

  • Validate first: Always check if your data is clean and fits the target type.
  • Use the right precision: When converting to types like DECIMAL, specify precision to avoid decimal drop-offs.
  • Avoid unnecessary conversion in WHERE clauses: They can kill your performance.
  • Think about nullables: Converting NULL behaves differently across types—check results.
  • When in doubt, default to CAST: It’s more cross-platform than CONVERT.

Final Thoughts

Data conversion in SQL is a cornerstone concept that enhances your ability to write safer, cleaner, and more powerful queries. Whether you’re cleaning up a dataset, building transformations for a pipeline, or just trying to fix a weird import error, becoming