Difference between FORMAT, CAST, and CONVERT in SQL

When working with SQL Server, three functions often come up that look similar but serve different purposes: FORMAT, CAST, and CONVERT. This guide breaks them down with syntax, examples, and all the formatting options you need.

FORMAT

Syntax

FORMAT (value, format [,culture])

• value → The expression to format (date, time, number).
• format → A .NET style format string (e.g., 'yyyy-MM-dd', 'C', 'P').
• culture (optional) → Defines the culture/language (e.g., 'en-US', 'fr-FR').

Examples

-- Date formatting
SELECT FORMAT('2025-06-17', 'yyyy/MM/dd')
-- Output: 2025/06/17

-- Numeric formatting
SELECT FORMAT(12345.6789, 'N2')
-- Output: 12,345.68

-- Currency (US)
SELECT FORMAT(12345.6789, 'C', 'en-US')
-- Output: $12,345.68

-- Currency (France)
SELECT FORMAT(12345.6789, 'C', 'fr-FR')
-- Output: 12 345,68 €

Common Date/Time Format Strings

• d → Short date (17/06/2025)
• D → Long date (Tuesday, 17 June 2025)
• t → Short time (14:30)
• T → Long time (14:30:59)
• M → Month day (June 17)
• Y → Year month (June 2025)
• yyyy-MM-dd → Custom (2025-06-17)

Common Numeric Format Strings

• N2 → Number with 2 decimals (1,234.57)
• C → Currency ($1,234.57)
• P → Percentage (123,457.00 %)
• 0.00 → Fixed-point decimal (12345.68)

CAST

Syntax

CAST (expression AS data_type)

• expression → The value you want to convert.
• data_type → The target SQL Server data type (e.g., VARCHAR, INT).
• length (optional) → For character/numeric data types.

Examples

-- Date to string
SELECT CAST(GETDATE() AS VARCHAR(20))
-- Output: Jun 17 2025 2:30PM

-- String to integer
SELECT CAST('123' AS INT)
-- Output: 123

-- Decimal to integer
SELECT CAST(123.45 AS INT)
-- Output: 123

Data Types You Can CAST To

• Character → CHAR, VARCHAR, TEXT
• Numeric → INT, BIGINT, DECIMAL, NUMERIC, FLOAT, REAL
• Date/Time → DATE, DATETIME, DATETIME2, SMALLDATETIME, TIME
• Other → BINARY, VARBINARY, XML, UNIQUEIDENTIFIER

CONVERT

Syntax

CONVERT(data_type, expression [,style])

• data_type → The target data type (like CAST).
• expression → The value to convert.
• style → Formatting style (for dates, times, or money).

Examples

-- Date to string with style code
SELECT CONVERT(VARCHAR, GETDATE(), 103)
-- Output: 17/06/2025

-- US format
SELECT CONVERT(VARCHAR, GETDATE(), 101)
-- Output: 06/17/2025

-- Currency formatting
SELECT CONVERT(VARCHAR, CAST(12345.678 AS MONEY), 1)
-- Output: 12,345.68

Style Codes for Dates (most common)

• 101 → US (mm/dd/yyyy)
• 103 → UK/FR (dd/mm/yyyy)
• 104 → Germany (dd.mm.yyyy)
• 105 → Italy (dd-mm-yyyy)
• 106 → (dd mon yyyy)
• 112 → ISO (yyyymmdd)

Style Codes for Money/Numbers

• 0 → Default with commas
• 1 → Commas + 2 decimals
• 2 → No commas + 2 decimals

Quick Recap

• FORMAT → Great for presentation, works with dates, times, and numbers.
• CAST → Simple data type conversion.
• CONVERT → Data type conversion with style control (dates & money).