CONVERT()

Syntax

How it works

CONVERT() converts an expression from one data type to another, much like CAST(). The difference is that CONVERT() is vendor specific: it is not part of the portable SQL standard the way CAST is, and the two databases that support it order the arguments in opposite ways.

In SQL Server the target type comes first: CONVERT(target_type, expression [, style]). The optional third argument, the style code, is what makes the SQL Server version genuinely useful, because it formats the result. Style 23 produces yyyy-mm-dd, style 101 produces mm/dd/yyyy, style 120 produces yyyy-mm-dd hh:mi:ss, and so on. This lets you turn a datetime into a specific text layout in one call.

In MySQL the expression comes first: CONVERT(expression, target_type). MySQL also has a second, unrelated form, CONVERT(expression USING charset), which converts a string between character sets rather than data types. MySQL has no style argument.

PostgreSQL does not use CONVERT for type conversion at all. Its CONVERT() function converts a binary string between text encodings. For changing a value data type in PostgreSQL you use CAST() or the :: shortcut. Because of all this, prefer CAST() when you want portable, standard type conversion, and reach for CONVERT only when you specifically need SQL Server style formatting or a MySQL character set change. See SQL data types explained for the types you can target.

Examples

SQL Server: format a date with a style code

-- style 23 gives yyyy-mm-dd, style 101 gives mm/dd/yyyy
SELECT
  CONVERT(VARCHAR(10), GETDATE(), 23)  AS iso_date,
  CONVERT(VARCHAR(10), GETDATE(), 101) AS us_date;

iso_date   | us_date
-----------+-----------
2026-07-05 | 07/05/2026

MySQL: convert a value to another type

-- MySQL puts the expression FIRST, then the type
SELECT CONVERT('2026-07-05', DATE)   AS as_date,
       CONVERT('42.50', DECIMAL(6,2)) AS as_number;

as_date    | as_number
-----------+----------
2026-07-05 |     42.50

MySQL: convert a string to a character set

-- The USING form changes character set, not data type
SELECT CONVERT('cafe' USING utf8mb4) AS text_utf8mb4;

text_utf8mb4
------------
cafe

SQL Server: number to text for display

-- Turn an integer into a padded string label
SELECT order_id,
       'ORD-' + CONVERT(VARCHAR(10), order_id) AS label
FROM orders;

order_id | label
---------+--------
     101 | ORD-101
     102 | ORD-102

Prefer CAST for portable type conversion

-- CAST works the same in SQL Server, MySQL and PostgreSQL
SELECT CAST('42.50' AS DECIMAL(6,2)) AS as_number,
       CAST('2026-07-05' AS DATE)     AS as_date;

as_number | as_date
----------+-----------
    42.50 | 2026-07-05

Common mistakes

-- SQL Server argument order used in MySQL: this errors
-- MySQL reads it as CONVERT(type, value), which is wrong
SELECT CONVERT(DATE, '2026-07-05');

-- MySQL wants the expression first, then the type
SELECT CONVERT('2026-07-05', DATE);

-- PostgreSQL: this does NOT cast to an integer
SELECT CONVERT('123', INTEGER);

-- PostgreSQL uses CAST or the :: shortcut for types
SELECT CAST('123' AS INTEGER) AS n,
       '123'::INTEGER          AS also_n;

-- Trying to remember which style number is which
SELECT CONVERT(VARCHAR(10), GETDATE(), 3);  -- what format is 3?

-- Be explicit: comment the intended format, or use
-- FORMAT() for a readable pattern (SQL Server 2012+)
SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS iso,  -- yyyy-mm-dd
       FORMAT(GETDATE(), 'yyyy-MM-dd')     AS iso2;

Performance

CONVERT() is cheap per row, but converting a column inside a WHERE or JOIN makes the predicate non sargable: the database can no longer use an index on that column and falls back to a full scan. Convert the constant or literal side of the comparison instead, and leave the indexed column untouched.

Storing values in the right type in the first place avoids most conversions. If you frequently convert a text column to a date, that column probably should be a DATE. Repeated conversions across millions of rows add up, and they also risk silent data loss when a value does not fit the target type.

For date and number formatting, remember that CONVERT() with a style produces a string. Do the formatting at the presentation layer or only in the final SELECT, not on columns you still need to filter, sort or join on, so the engine keeps working with the native typed values.

Interview questions

SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS iso_date;