SQL Date & Time Functions: FORMAT, CONVERT, and CAST

SQL databases store dates, times, and numeric data in strict binary formats. However, reporting tools, user interfaces, and downstream application pipelines require data presented in specific visual layouts or converted into precise target data types. Understanding how to control both the presentation and the data type of temporal and numeric values is fundamental to writing robust SQL queries.

This guide provides a comprehensive overview of date formats, format specifiers, and the three primary functions used for type transformation and string formatting in SQL Server: FORMAT(), CONVERT(), and CAST().

1. Understanding Date Representation & Format Specifiers

A datetime value consists of both numerical digits and structural characters (such as hyphens, slashes, or colons). In SQL, custom presentation layouts are constructed using format specifiers—token codes that map to specific date/time components.

Raw Value:  2025-01-05 14:30:00
Specifier:  yyyy-MM-dd HH:mm:ss

Core Date/Time Format Specifiers

Critical Note on Case Sensitivity: Format specifiers are case-sensitive. Capital MM strictly represents the Month, whereas lowercase mm represents Minutes. Confusing these two will result in erroneous outputs (e.g., rendering minutes where months were expected).

Regional Date Standards

Different global regions represent the exact same calendar date using different component orders:

Conceptually: Relational database systems like SQL Server internally utilize the ISO standard (yyyy-MM-dd) for storage and internal evaluation to guarantee consistency across execution engines.

2. Core Mental Model: Formatting vs. Casting

Before executing operations, it is necessary to differentiate between Formatting and Casting.

• Formatting (FORMAT): Modifies the visual presentation of a value without altering its conceptual meaning. The output of a formatting function is almost universally a string (NVARCHAR).
• Casting (CAST / CONVERT): Modifies the underlying storage data type from one data type to another (e.g., converting a text string '123' into a true integer 123, or stripping time metadata by casting DATETIME to DATE).

3. The FORMAT() Function: Syntax & Custom Specifiers

The FORMAT() function converts a date, time, or numeric value into a formatted string based on a specified format mask and an optional culture setting.

Syntax

FORMAT(value, format_string [, culture])

• value: The target expression (date, time, or number) to format.
• format_string: A string containing format specifier tokens (e.g., 'yyyy-MM-dd').
• culture: (Optional) A string specifying the locale/culture (e.g., 'en-US', 'ja-JP', 'fr-FR'). Defaults to the session language if omitted.

Extracting Date Components via Token Length

By varying the number of token characters provided to FORMAT(), you control the granularity and style of the returned text:

SELECT 
    creation_time,
    -- Day specifiers
    FORMAT(creation_time, 'dd')   AS day_digits,       -- '05'
    FORMAT(creation_time, 'ddd')  AS day_short_name,   -- 'Sun'
    FORMAT(creation_time, 'dddd') AS day_full_name,    -- 'Sunday'
    
    -- Month specifiers
    FORMAT(creation_time, 'MM')   AS month_digits,     -- '01'
    FORMAT(creation_time, 'MMM')  AS month_short_name, -- 'Jan'
    FORMAT(creation_time, 'MMMM') AS month_full_name   -- 'January'
FROM sales_orders;

Standard Regional Transformations

SELECT 
    creation_time,
    -- USA Format: MM-dd-yyyy
    FORMAT(creation_time, 'MM-dd-yyyy') AS usa_format,
    
    -- European Format: dd-MM-yyyy
    FORMAT(creation_time, 'dd-MM-yyyy') AS european_format,
    
    -- Culture-aware formatting (Japan)
    FORMAT(creation_time, 'd', 'ja-JP')  AS japan_culture_format
FROM sales_orders;

4. Advanced Date Manipulation & Aggregations with FORMAT()

Constructing Custom Display Strings

You can combine static string literals, custom specifiers, and date functions to generate complex business outputs:

-- Target Output Format: "day Sun Jan Q1 2025 02:30:00 PM"
SELECT 
    'day ' 
    + FORMAT(creation_time, 'ddd MMM') 
    + ' Q' + DATE_NAME(quarter, creation_time) 
    + ' ' 
    + FORMAT(creation_time, 'yyyy hh:mm:ss tt') AS custom_complex_format
FROM sales_orders;

Formatting Prior to Aggregation (GROUP BY)

A common analytics requirement is grouping daily transactional data into monthly or quarterly summaries. Using FORMAT() inside a GROUP BY clause modifies date granularity directly for presentation reporting:

-- Aggregating Sales volume by Month-Year (e.g., 'Jan-25')
SELECT 
    FORMAT(order_date, 'MMM-yy') AS order_month,
    COUNT(order_id)             AS total_orders,
    SUM(order_amount)           AS total_sales
FROM sales_orders
GROUP BY 
    FORMAT(order_date, 'MMM-yy');

5. Number Formatting with FORMAT()

In addition to dates, FORMAT() supports specifier masks for formatting numeric values into localized strings with currency symbols, thousand separators, or percentage markers.

Core Numeric Specifiers

Code Demonstration

SELECT 
    12345.678 AS raw_number,
    -- General Numeric with grouping
    FORMAT(12345.678, 'N') AS formatted_numeric,       -- '12,345.68'
    
    -- US Dollar Currency
    FORMAT(12345.678, 'C', 'en-US') AS us_currency,   -- '$12,345.68'
    
    -- Euro Currency (French culture uses space separator and trailing €)
    FORMAT(12345.678, 'C', 'fr-FR') AS euro_currency,  -- '12 345,68 €'
    
    -- Percentage
    FORMAT(0.854, 'P') AS formatted_percentage        -- '85.40%'
;

6. The CONVERT() Function: Dual-Purpose Casting & Styling

CONVERT() is a SQL Server-specific function that performs type casting. Uniquely, when converting dates to string types (VARCHAR/NVARCHAR), CONVERT() accepts integer style codes to format the output.

Syntax

CONVERT(target_data_type, expression [, style_code])

• target_data_type: The desired data type (e.g., INT, DATE, VARCHAR(30)).
• expression: The input value or column to convert.
• style_code: (Optional) An integer mapping to a predefined standard format layout.

Pure Data Type Conversion (Casting)

-- Convert String to Integer
SELECT CONVERT(INT, '123') AS parsed_int;

-- Convert DATETIME to DATE (Strips time component)
SELECT 
    creation_time,
    CONVERT(DATE, creation_time) AS date_only
FROM sales_orders;

Combined Casting and Formatting via Style Codes

By specifying VARCHAR as the target type and providing a style_code, CONVERT() transforms and formats the date simultaneously:

SELECT 
    creation_time,
    -- Style 101: USA Standard (MM/dd/yyyy)
    CONVERT(VARCHAR, creation_time, 101) AS usa_style_101,
    
    -- Style 103: European Standard (dd/MM/yyyy)
    CONVERT(VARCHAR, creation_time, 103) AS euro_style_103,
    
    -- Style 112: ISO Standard Unseparated (yyyyMMdd)
    CONVERT(VARCHAR, creation_time, 112) AS iso_unseparated_112,
    
    -- Style 6: Short Date (dd MMM yy)
    CONVERT(VARCHAR, creation_time, 6)   AS short_date_6
FROM sales_orders;

7. The CAST() Function: Pure ANSI-Standard Type Conversion

CAST() is the standard SQL function for converting an expression from one data type to another. Unlike FORMAT() or CONVERT(), CAST() does not accept format specifier strings or style codes. It performs clean, unformatted type transformations according to ANSI SQL standards.

Syntax

CAST(expression AS target_data_type)

Code Demonstration

-- 1. String to Integer Transformation
SELECT CAST('123' AS INT) AS integer_value;

-- 2. Integer to String Transformation
SELECT CAST(123 AS VARCHAR(10)) AS string_value;

-- 3. String to Date Transformation
SELECT CAST('2025-01-05' AS DATE) AS date_value;

-- 4. String to Datetime Transformation
SELECT CAST('2025-01-05 14:30:00' AS DATETIME) AS datetime_value;

-- 5. Truncating Time from DATETIME column
SELECT 
    creation_time,
    CAST(creation_time AS DATE) AS clean_date
FROM sales_orders;

8. Real-World Architectural Workflow: Data Cleaning & Integration

In production data engineering pipelines, raw ingestion tables often collect dates stored as inconsistent strings or non-standard regional layouts from diverse source systems (e.g., CSV extracts, third-party APIs).

Before data can be analyzed, it must undergo a unified cleanup workflow:

1. Ingestion: Raw dates land as disparate strings.
2. Standardization (Casting): Use CAST() or CONVERT() to parse raw strings into strong native date types (DATE/DATETIME) inside warehouse tables.
3. Presentation (Formatting): Use FORMAT() or CONVERT() with style codes at query time to supply BI dashboards with required localized layouts.

9. Comparative Synthesis: CAST vs. CONVERT vs. FORMAT

Selecting the correct function depends on whether you require strict data typing, specific standard formatting, performance optimization, or culture-aware formatting.

Performance Tip: Avoid using FORMAT() inside WHERE clauses, JOIN conditions, or queries processing millions of records. Because FORMAT() relies on the .NET CLR interop layer, invoking it over large rowsets incurs significant CPU overhead compared to native CAST() or CONVERT().

10. Summary and Key Takeaways

• Formatting vs. Casting: Formatting changes the visual presentation of data into a string (VARCHAR/NVARCHAR). Casting changes the underlying database data type (e.g., from VARCHAR to INT or DATETIME to DATE).
• Format Specifiers are Case-Sensitive: Always distinguish between capital MM (Month) and lowercase mm (Minutes).
• ISO 8601 Standard: SQL Server natively follows the international standard format (yyyy-MM-dd).
• FORMAT() Flexibility: FORMAT(value, mask [, culture]) is ideal for custom date masks, strings, localized culture layouts, and formatting numbers as currency ('C') or percentages ('P').
• CONVERT() Dual-Utility: CONVERT(datatype, value [, style]) provides both type casting and date formatting via predefined integer style codes (e.g., 101 for US, 103 for Europe, 112 for unseparated ISO).
• CAST() Standard Compliance: CAST(value AS datatype) is the ANSI SQL standard for type conversion. It accepts no formatting parameters and offers optimal performance.
• Aggregating on Custom Granularity: Use FORMAT(date_col, 'MMM-yy') in both the SELECT and GROUP BY clauses to aggregate metrics into custom temporal buckets.
• Engine Overhead: Reserve FORMAT() primarily for final-layer projection queries (SELECT clauses) serving reports; rely on CAST() and CONVERT() for backend ETL processing, filtering, and joining logic.