SQL Server CONVERT() Date Style Codes Explained (With Examples)

In SQL Server, dates are stored in one format but often need to be displayed in another. That’s where the CONVERT() function can help. It lets you take a date value and turn it into a string in whatever format you need, whether that’s MM/DD/YYYY for an American audience or DD-MM-YYYY for a universally recognized date format.

The tricky part when using this function is the style code. This a number you pass into CONVERT() that tells it which format to use. There are dozens of style codes, and they’re not exactly intuitive to memorise. This article breaks down the most useful ones with real examples so you can find what you need and move on.

The Basic Syntax

The CONVERT() function can work with as little as two arguments, but if we want to specify the output format, we need to provide three arguments:

CONVERT(data_type, value, style)

data_type – usually VARCHAR when you’re converting a date to a readable string
value – the date or datetime column you want to format
style – optional number that controls the output format

So it looks something like this:

SELECT CONVERT(VARCHAR, GETDATE(), 103)

That would return today’s date formatted as DD/MM/YYYY. The 103 is the style code for the DD/MM/YYYY format.

If you omit the style code, SQL Server falls back to a default style based on the target data type or your database’s local configuration.

The Most Common Date Style Codes

SQL Server style codes come in two flavours: two-digit and three-digit. The three-digit version (adding 100 to the two-digit code) includes a four-digit year instead of a two-digit one. In most cases, you’ll probably want the four-digit year, so the three-digit codes are what you’ll need here.

Examples of four digit year formats:

Style Code	Format	Example Output
101	MM/DD/YYYY	06/24/2026
103	DD/MM/YYYY	24/06/2026
104	DD.MM.YYYY	24.06.2026
105	DD-MM-YYYY	24-06-2026
106	DD Mon YYYY	24 Jun 2026
107	Mon DD, YYYY	Jun 24, 2026
110	MM-DD-YYYY	06-24-2026
111	YYYY/MM/DD	2026/06/24
112	YYYYMMDD	20260624
120	YYYY-MM-DD HH:MM:SS	2026-06-24 03:09:42
126	ISO 8601 (YYYY-MM-DDTHH:MM:SS:MMM)	2026-06-24T03:09:42.027

And here are the two-digit year versions:

Style Code	Format	Example Output
1	MM/DD/YY	06/24/26
3	DD/MM/YY	24/06/26
4	DD.MM.YY	24.06.26
5	DD-MM-YY	24-06-26
6	DD Mon YY	24 Jun 26
7	Mon DD, YY	Jun 24, 26
10	MM-DD-YY	06-24-26
11	YY/MM/DD	26/06/24
12	YYMMDD	260624

Not all formats have a two digit equivalent. For example, there’s no two year equivalent of 120 or 126.

Examples You Can Run

Here’s a quick query that runs several style codes at once so you can see them side by side:

SELECT
  CONVERT(VARCHAR, GETDATE(), 101) AS us_format,
  CONVERT(VARCHAR, GETDATE(), 103) AS uk_format,
  CONVERT(VARCHAR, GETDATE(), 106) AS readable_format,
  CONVERT(VARCHAR, GETDATE(), 112) AS compact_format,
  CONVERT(VARCHAR, GETDATE(), 120) AS datetime_format;

Running that gives you something like:

us_format                      uk_format                      readable_format                compact_format                 datetime_format               
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
06/24/2026                     24/06/2026                     24 Jun 2026                    20260624                       2026-06-24 03:26:39

Style Codes for Time as Well as Date

If your column is a DATETIME rather than just a DATE, some style codes will include the time component automatically. Others won’t.

Style 120 is the go-to when you need both date and time in a clean, readable format:

SELECT CONVERT(VARCHAR, GETDATE(), 120);

Output:

2026-06-24 03:28:32

Style 126 gives you the ISO 8601 format, which is useful when you’re passing dates to an API or another system that expects a standardised format:

SELECT CONVERT(VARCHAR, GETDATE(), 126);

Result:

2026-06-24T03:28:52.020

For just the time portion on its own, styles 8 and 108 both return HH:MM:SS:

SELECT CONVERT(VARCHAR, GETDATE(), 108);

Output:

03:29:23

Using `CONVERT()` on a Table Column

GETDATE() is handy for testing, but in real queries you’ll usually be converting a column. It works exactly the same way:

SELECT
  order_id,
  CONVERT(VARCHAR, order_date, 103) AS formatted_date
FROM orders;

If order_date is stored as a DATETIME and you only want the date part without the time, style 103 handles that cleanly since it only outputs the date portion.

`CONVERT()` vs `FORMAT()` vs `CAST()`

SQL Server gives you a few different ways to handle date formatting, and it’s worth knowing when to use which.

CAST() is the simplest option. It converts between data types but gives you no control over the output format. If you just need a date as a string and don’t care how it looks, CAST(order_date AS VARCHAR) will do it.

FORMAT() is more flexible than CONVERT() and lets you use custom format strings like 'dd MMMM yyyy' to get output like 24 June 2026. The downside is that it’s significantly slower on large datasets because it relies on .NET formatting under the hood. For quick reports or small queries it’s fine, but avoid it inside queries that scan millions of rows.

CONVERT() sits in the middle. It’s faster than FORMAT(), gives you a solid set of pre-built formats, and covers the vast majority of real-world needs.

A Few Things to Watch Out For

A couple of common gotchas when working with CONVERT() and date styles:

Specifying VARCHAR length – if you write VARCHAR without a length, SQL Server defaults to 30 characters, which is usually fine for dates but can silently truncate longer strings in other contexts. It’s a good habit to be explicit: VARCHAR(20).
Two-digit vs four-digit years – style 3 gives you DD/MM/YY while style 103 gives you DD/MM/YYYY. Always use the three-digit version unless you specifically need a two-digit year.
NULL values – CONVERT() will return NULL if the input value is NULL. Wrap it in ISNULL() or COALESCE() if you need a fallback value.
Converting strings back to dates – CONVERT() works the other way too. If you’re taking a string and converting it to a DATE or DATETIME, the style code tells SQL Server how to interpret the string’s format.

Quick Reference Card

If you just need a cheat sheet to bookmark, here are the style codes grouped by use case:

Style Code	Format	Category	Notes
101	MM/DD/YYYY	Date only	US
103	DD/MM/YYYY	Date only	UK / Australia
104	DD.MM.YYYY	Date only	German
106	DD Mon YYYY	Date only	Readable, no commas
107	Mon DD, YYYY	Date only	Readable, with comma
111	YYYY/MM/DD	Date only	Japan
112	YYYYMMDD	Date only	Compact, good for filenames or sorting
120	YYYY-MM-DD HH:MM:SS	Date and time	Clean, widely used
126	YYYY-MM-DDTHH:MM:SS.mmm	Date and time	ISO 8601, good for APIs
108	HH:MM:SS	Time only

Most of the time you’ll probably use 103, 112, or 120. The rest are there when you need them.