(opens in a new tab)

SQL Server CONVERT Date Format Styles Explained

September 25, 2026 by Chat2DB Team

SQL Server does not have a TO_CHAR or DATE_FORMAT function with pattern letters. For most of its history, the way to turn a date into a specific text format, or to parse text in a specific format, has been CONVERT with a numeric style code. Style 101 gives US dates, 103 gives British dates, 112 gives compact ISO dates, 120 gives the ODBC canonical format, and so on.

The style numbers are not memorable, and a few of them behave in ways that surprise people: two-digit years, silent truncation, milliseconds that appear and disappear, and strings that parse differently depending on the session language. This guide lists every commonly used style with real output for one fixed value, then covers length choice, CONVERT vs CAST vs FORMAT, parsing strings safely with TRY_CONVERT, and the ISO 8601 formats that avoid language problems entirely.

CONVERT Syntax

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

• data_type is the target type. For formatting, it is usually varchar or nvarchar. For parsing, it is date, datetime, datetime2, time, or datetimeoffset.
• expression is the value to convert.
• style is an integer that controls the format. It applies in both directions: when converting a date to a string it controls the output, and when converting a string to a date it controls how the input is interpreted.

A basic example:

SELECT CONVERT(varchar(10), GETDATE(), 101) AS us_date;
-- 09/25/2026 (on the day this article was written)

The Style Code Table

All examples below use the same value so you can compare outputs directly:

DECLARE @d datetime = '2026-09-25T14:05:09.123';

The rule for the leading digit is simple: styles below 100 (with a few exceptions) output a two-digit year, and adding 100 gives the same layout with a four-digit year.

Notes on the table:

• Space padding in styles 0, 100, 9, 109, and 22. Single-digit hours are padded with a space, not a zero, so the real output of style 0 is Sep 25 2026 followed by two spaces and then 2:05PM. Rendered tables collapse repeated spaces, but your application will see both of them. The same applies to single-digit days in styles 0, 100, 9, and 109.
• Styles 0/100, 9/109, 13/113, 14/114, 20/120, and 21/121 are identical pairs. The formats already include a four-digit year (or no year at all), so the "century" variant makes no difference. Style 8 and 108 are also identical, and style 24 is another alias for hh:mi:ss.
• Style 126 drops zero milliseconds. If the millisecond part is 0, style 126 outputs 2026-09-25T14:05:09 without a fractional part. Code that parses the result must accept both shapes.
• Style 127 is intended for datetimeoffset values. The value is expressed in UTC with a trailing Z. For a plain datetime, which carries no offset, the output looks like style 126. Style 127 is also the right style for parsing strings that end in Z.
• Styles 130 and 131 produce Hijri (Islamic calendar) dates. They are rarely needed outside specific locales and are omitted from the table.
• Month names follow the session language. Styles that output mon use the current SET LANGUAGE setting, so the same query can produce Sep under us_english and a different abbreviation under another language.

You can reproduce the table in one query:

DECLARE @d datetime = '2026-09-25T14:05:09.123';
 
SELECT s.style,
       CONVERT(varchar(40), @d, s.style) AS formatted
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),
             (20),(21),(22),(23),
             (100),(101),(102),(103),(104),(105),(106),(107),(108),(109),
             (110),(111),(112),(113),(114),(120),(121),(126),(127)) AS s(style)
ORDER BY s.style;

If you would rather not memorize the numbers, the free SQL Server date format converter (opens in a new tab) from Chat2DB shows the output of each style for a date you enter and generates the matching CONVERT expression.

Two-Digit vs Four-Digit Years

Two-digit year styles (1 through 7, 10, 11, 12, and 22) are a common source of bugs:

• Output is ambiguous. 09/25/26 could be 1926 or 2026 to anyone reading it later.
• Parsing depends on a server setting. When SQL Server reads a two-digit year, it uses the two digit year cutoff configuration option, which defaults to 2049. With the default, 49 becomes 2049 and 50 becomes 1950.

SELECT CONVERT(date, '01/15/49', 1) AS y49,  -- 2049-01-15
       CONVERT(date, '01/15/50', 1) AS y50;  -- 1950-01-15

For anything stored, exchanged, or logged, use the four-digit version of the style (the 1xx codes) or, better, an ISO 8601 format.

Choosing the varchar Length

If you omit the length, as in CONVERT(varchar, @d, 121), SQL Server uses a default length of 30 for CONVERT and CAST. That is enough for every style in the table above, but relying on it is fragile. Specify the length explicitly:

Too short a length truncates silently. That is sometimes used on purpose:

-- Take only the date part of style 120
SELECT CONVERT(varchar(10), @d, 120);  -- 2026-09-25
 
-- Take only hh:mi from style 108
SELECT CONVERT(varchar(5), @d, 108);   -- 14:05

The trick is fine when intentional, but it hides mistakes when it is not. If a format suddenly loses its seconds or milliseconds, check the length first.

Also remember that datetime2 and datetimeoffset carry up to seven fractional digits. Style 121 on a datetime2(7) value produces 2026-09-25 14:05:09.1230000, which is 27 characters, not 23.

CONVERT vs CAST vs FORMAT

CAST

CAST is ANSI standard and has no style argument:

SELECT CAST(@d AS varchar(30));                      -- Sep 25 2026  2:05PM
SELECT CAST(CAST(@d AS datetime2(3)) AS varchar(30)); -- 2026-09-25 14:05:09.123

For datetime and smalldatetime, CAST to a string uses style 0, which is rarely what you want. For date, time, datetime2, and datetimeoffset, the default is style 121. Use CAST for type changes that do not involve a display format, such as CAST(@d AS date) to drop the time portion. That is also much cheaper than a round trip through a string.

CONVERT

CONVERT is SQL Server specific but gives you control over the format through the style code. It is fast because it is implemented natively in the engine, and it is the right choice for bulk formatting and for parsing strings in a known layout.

FORMAT

FORMAT, available since SQL Server 2012, uses .NET format strings and cultures:

SELECT FORMAT(@d, 'yyyy-MM-dd HH:mm:ss');         -- 2026-09-25 14:05:09
SELECT FORMAT(@d, 'dd MMMM yyyy', 'fr-FR');       -- 25 septembre 2026
SELECT FORMAT(@d, 'dddd, MMMM d, yyyy', 'en-US'); -- Friday, September 25, 2026

It is far more flexible than style codes: any pattern, any culture, full month and day names. The trade-offs:

• Performance. FORMAT relies on the .NET CLR. Its per-row cost is noticeably higher than CONVERT, which matters in queries that format many rows. Measure it on your own data before using it in large reports or in WHERE clauses.
• Case-sensitive patterns. MM is month and mm is minutes; HH is 24-hour and hh is 12-hour. Mixing them up produces plausible but wrong output.
• Return type and errors. It returns nvarchar, and it returns NULL rather than an error if the culture is invalid.
• Formatting only. FORMAT cannot parse strings into dates.

A reasonable rule: use CONVERT with a style for standard formats and large volumes, and FORMAT for human-facing output that needs culture-aware names or a layout no style provides. Often the best choice is to format in the application layer and return a real date type from SQL.

Converting Strings to Dates with a Style

The style argument also tells SQL Server how to read a string. This is how you parse non-ISO input without changing session settings:

SELECT CONVERT(date, '25/09/2026', 103) AS british,   -- 2026-09-25
       CONVERT(date, '09/25/2026', 101) AS us,        -- 2026-09-25
       CONVERT(date, '25.09.2026', 104) AS german,    -- 2026-09-25
       CONVERT(date, '20260925', 112)   AS iso_basic; -- 2026-09-25
 
SELECT CONVERT(datetime2(0), '2026-09-25 14:05:09', 120);
SELECT CONVERT(datetimeoffset, '2026-09-25T14:05:09Z', 127);

If the string does not match the style, CONVERT raises an error, which aborts the statement. When loading data from files or user input, that is rarely what you want.

TRY_CONVERT

TRY_CONVERT accepts the same arguments but returns NULL when conversion fails:

SELECT TRY_CONVERT(date, '31/02/2026', 103) AS bad_day,    -- NULL
       TRY_CONVERT(date, '25/09/2026', 101) AS wrong_style, -- NULL (month 25)
       TRY_CONVERT(date, '25/09/2026', 103) AS ok;          -- 2026-09-25

This makes it easy to find bad rows in a staging table before moving data:

SELECT raw_value
FROM staging_orders
WHERE raw_value IS NOT NULL
  AND TRY_CONVERT(date, raw_value, 103) IS NULL;

TRY_CAST exists too but has no style argument, so it depends on session settings. TRY_PARSE is culture-aware like FORMAT and shares its CLR cost.

ISO 8601 and Language-Independent Literals

The safest way to write date literals in SQL Server is to use formats that are interpreted the same way under every language and DATEFORMAT setting:

• 'YYYYMMDD', for example '20260925', for dates.
• 'YYYY-MM-DDThh:mm:ss[.mmm]', for example '2026-09-25T14:05:09.123', for date and time. The T is required.

Both are unambiguous for datetime, smalldatetime, date, datetime2, and datetimeoffset.

The YYYY-MM-DD trap

The format 'YYYY-MM-DD' without a time looks like ISO 8601, and for date, datetime2, and datetimeoffset it is always interpreted correctly. For the older datetime and smalldatetime types, however, it depends on DATEFORMAT:

SET LANGUAGE british;  -- implies DATEFORMAT dmy
 
SELECT CAST('2026-09-25' AS date);       -- 2026-09-25, fine
SELECT CAST('2026-09-25' AS datetime);   -- error: out-of-range value
SELECT CAST('20260925' AS datetime);     -- 2026-09-25 00:00:00.000, fine
 
SET LANGUAGE us_english;

Under british, SQL Server reads '2026-09-25' as year-day-month for datetime, finds month 25, and fails with error 242. Under us_english the same statement works, which is exactly why this bug escapes testing and appears only on a server or login with a different default language.

DATEFORMAT and SET LANGUAGE

SET DATEFORMAT controls the order in which ambiguous string dates are read:

SET DATEFORMAT dmy;
SELECT CAST('05/09/2026' AS date);  -- 2026-09-05
 
SET DATEFORMAT mdy;
SELECT CAST('05/09/2026' AS date);  -- 2026-05-09

SET LANGUAGE changes both DATEFORMAT and month names. Each login has a default language, so two applications running the same query can get different results. Explicit style codes in CONVERT, or the unambiguous ISO formats above, remove that dependency.

Recommendations

1. Store dates as date types, not strings. Use date, datetime2, or datetimeoffset. Format only at the edge, for display or export.
2. Use ISO formats for exchange. Style 126 (or 127 for UTC with Z) for JSON and APIs, style 112 or 23 for date-only files.
3. Use four-digit year styles. Prefer 101, 103, and friends over 1, 3, and friends.
4. Always specify the varchar length. Know when you are truncating on purpose.
5. Parse with an explicit style and TRY_CONVERT. Never rely on the session language to interpret input.
6. Avoid functions on indexed columns in WHERE. WHERE CONVERT(varchar(10), order_date, 120) = '2026-09-25' prevents index seeks. Use a range instead:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '20260925'
  AND order_date <  '20260926';

7. Reserve FORMAT for presentation. It is convenient, but keep it out of large result sets and filters.

Summary

CONVERT style codes are terse but predictable once you know the pattern: add 100 for a four-digit year, use 112, 23, 120, and 126 for ISO-style output, and watch out for space padding and silent truncation. For parsing, give CONVERT or TRY_CONVERT an explicit style, and write literals as 'YYYYMMDD' or 'YYYY-MM-DDThh:mm:ss' so they survive any language setting.

If you work with SQL Server regularly, a good client makes testing these expressions quick. Chat2DB (opens in a new tab) lets you run and compare queries across SQL Server and other databases, and its AI assistant can draft the right CONVERT or FORMAT call from a plain-language description. For connection setup, see the SQL Server connection string guide.