Formatting & Casting: FORMAT, CONVERT, CAST

The database stores 2025-08-20, but an Indian report wants 20/08/2025, a poster wants 20 Aug 2025, and an e-mail wants Wednesday. Separately, you sometimes need to change a value's type — turn the text '123' into a number, or a number into text to glue onto a sentence. That is formatting and casting.

Definition

Formatting changes how a value looks (it returns text). Casting changes a value's data type (e.g. VARCHAR → INT, VARCHAR → DATE).

Syntax

FORMAT(value, format_string [, culture])
CONVERT(data_type, value [, style])
CAST(value AS data_type)

FORMAT — show a date your way

SELECT
    FORMAT(CAST('2025-08-20' AS DATE), 'dd/MM/yyyy')  AS indian_style,   -- '20/08/2025'
    FORMAT(CAST('2025-08-20' AS DATE), 'dd MMM yyyy') AS poster,         -- '20 Aug 2025'
    FORMAT(CAST('2025-08-20' AS DATE), 'MMMM yyyy')   AS month_year,     -- 'August 2025'
    FORMAT(CAST('2025-08-20' AS DATE), 'dddd')        AS day_name;       -- 'Wednesday'

FORMAT returns a string, and the pattern is case-sensitive: MM = month, mm = minutes.

Format specifiers (sample value 2025-08-20 18:55:45):

FORMAT — numbers too

Tip: FORMAT(1234.56, 'C', 'en-IN') shows Indian rupees (₹). The output is always text, so you can't do maths on it afterwards.

CONVERT — change type (+ style)

SELECT
    CONVERT(INT, '124')                          AS to_int,     -- 124
    CONVERT(VARCHAR, 750)                        AS to_text,    -- '750'
    CONVERT(VARCHAR, CAST('2025-08-20' AS DATE), 103) AS uk_india, -- '20/08/2025'
    CONVERT(VARCHAR, CAST('2025-08-20' AS DATE), 101) AS us;       -- '08/20/2025'

Common CONVERT date styles (sample 2025-08-20 18:55:45):

Rule of thumb: styles below 100 give a 2-digit year, 100+ give a 4-digit year.

CAST — change type (ANSI standard)

Example — CAST to build a sentence

SELECT
    CONCAT(name, ' scored ', CAST(runs AS VARCHAR(10)), ' runs') AS line
FROM players;

(CONCAT would convert the number automatically, but with the + operator in SQL Server name + ' scored ' + runs fails — SQL tries to turn the text into an INT. CAST makes the intent explicit.)

CAST vs CONVERT vs FORMAT — which one?

Use CAST by default, CONVERT when you need a specific date style, and FORMAT for presentation-only output on small result sets.

Common mistakes

• ❌ FORMAT(d, 'dd/mm/yyyy') → minutes instead of month! ✅ 'dd/MM/yyyy'.
• ❌ Expecting CAST(3.9 AS INT) = 4. ✅ It truncates to 3; use ROUND first.
• ❌ Sorting by a FORMAT result ('20/08/2025') — text sorts alphabetically, not by date. ✅ Sort by the original date column.
• ❌ CAST('hello' AS INT) → conversion error. ✅ Validate first (ISDATE for dates) or use TRY_CAST, which returns NULL instead of failing.

SQL Server vs MySQL / PostgreSQL

Note: FORMAT and CONVERT are SQL Server features, so their examples below are shown as SQL Server-only code. CAST between numbers and text works in the browser playground.