SQL CONVERT

What is SQL CONVERT?

CONVERT changes an expression from one data type to another, optionally formatting the result.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CONVERT: SQL Server (all versions), Azure SQL Database, Sybase ASE. Partially supported in MySQL/MariaDB with different syntax. Not available in PostgreSQL or SQLite. Oracle has a CONVERT for character sets only.

SQL CONVERT Full Explanation

CONVERT is a scalar function that returns the input expression translated to a specified data type. In SQL Server it behaves like CAST but also accepts an optional style argument that controls the textual representation of date-time and monetary values. When length is supplied, character and binary types are truncated or padded to that size. CONVERT obeys implicit-conversion rules first, then applies explicit conversion; if the target type is incompatible, the statement fails with error 245. Using style 0-130 formats dates per ISO, US, or regional standards, while 0 keeps default session format. Unlike CAST, CONVERT can be used inside SELECT, WHERE, ORDER BY, INSERT, UPDATE, and procedural code. It is deterministic except when style affects string output. Large object types (TEXT, NTEXT, IMAGE) are deprecated; convert them to varchar(max), nvarchar(max), or varbinary(max).

SQL CONVERT Syntax

-- SQL Server
CONVERT(target_data_type[(length)], expression [, style]);

SQL CONVERT Parameters

target_data_type (data type) - Required. The desired data type of the result.
length (integer) - Optional. Size for char, nchar, varchar, nvarchar, binary, or varbinary.
expression (any) - Required. The value to convert.
style (integer) - Optional. Date/time or float style code (0-130).

Example Queries Using SQL CONVERT

-- Return current date as ISO 8601 string
SELECT CONVERT(varchar(10), GETDATE(), 120);

-- Convert string to DATETIME
SELECT CONVERT(datetime, '2024-06-01 12:30:00', 120);

-- Cast float to money with style 1 (comma thousands)
SELECT CONVERT(varchar(20), 1234567.89, 1);

-- Use in WHERE clause
SELECT order_id, order_date
FROM sales
WHERE order_date >= CONVERT(date, '2024-01-01');

Expected Output Using SQL CONVERT

Each query returns the expression in the target data type
First example returns '2024-06-01', second returns a DATETIME value, third returns '1,234,567
89', fourth filters rows from 1 Jan 2024 onward

Use Cases with SQL CONVERT

Format DATETIME columns for reports without changing underlying data.
Convert strings from external sources to native types before inserts.
Change numeric precision or scale when aggregating.
Filter or join tables with mismatched data types.

Common Mistakes with SQL CONVERT

Supplying an invalid style code (raises error 9819).
Forgetting length so long strings truncate to 30 characters (default for varchar).
Expecting implicit rounding rules different from target type precision.
Using styles on non-character output (style ignored).

First Introduced In

Sybase SQL Server 4.x (carried into Microsoft SQL Server 6.0)

Frequently Asked Questions

What is the difference between CONVERT and CAST?

CONVERT includes a style parameter for formatting output, while CAST follows the ANSI standard and provides no formatting options.

When should I use TRY_CONVERT instead of CONVERT?

Use TRY_CONVERT when the input may not convert cleanly. TRY_CONVERT returns NULL instead of throwing an error, keeping your query running.

How do I format a DATETIME as ISO 8601?

Call CONVERT with style 126 or 127:SELECT CONVERT(varchar(23), GETDATE(), 126);

Can I convert TEXT or NTEXT using CONVERT?

Yes, but Microsoft discourages it because those types are deprecated. Convert them to varchar(max) or nvarchar(max) for future-proof code.