How to Format Dates in SQL Server (A Beginner’s Guide)

Dates in SQL Server can be surprisingly tricky if you’re new to the game. The way dates are stored is not always the way you want them displayed, and figuring out how to convert one to the other is one of those things every beginner eventually Googles. So let’s walk through it clearly.

The Two Functions You Need to Know

SQL Server gives you two main ways to format dates:

FORMAT()
CONVERT()

These two functions do similar things but work differently. FORMAT() is newer, more flexible, and easier to read. CONVERT() is older but faster and still widely used.

For most beginners, FORMAT() is the more intuitive starting point.

Using `FORMAT()` to Format a Date

The FORMAT() function takes a date value and a format string, then returns the result as text. Here’s the basic syntax:

FORMAT( value , format [ , culture ] )

Here’s a simple example:

DECLARE @d DATE = '2035-03-24';
SELECT  
    @d AS "Raw Date",
    FORMAT(@d, 'D') AS "Formatted Date";

Result:

Raw Date                  Formatted Date          
------------------------  ------------------------
2035-03-24T00:00:00.000Z  Saturday, March 24, 2035

The DECLARE line sets up a date variable so the examples are easy to run and test yourself. In practice, you’d replace @d with your actual column name.

When formatting the above date, I used D as the format. This is a standard date format string. This is a convenient format string that tells SQL Server to format the date in a commonly used format. It’s a handy shortcut.

In this example we can see that the raw date is output with the time component. The actual output will depend on your client, and you may just see 2035-03-24. Either way, we can see that the formatted date looks a lot easier for us humans to read than the raw date.

There are other standard date/time format strings, such as F, which is similar to D, but that it also includes the time component in the output.

If you want more control over your date formats, you can use a custom date format string. These allow you to specify exactly where each date/time component goes.

Some examples:

DECLARE @d DATE = '2035-03-24';
SELECT  FORMAT(@d, 'MM/dd/yyyy') AS "MM/dd/yyyy",
        FORMAT(@d, 'dd-MM-yyyy') AS "dd-MM-yyyy",
        FORMAT(@d, 'yyyy-MM-dd') AS "yyyy-MM-dd",
        FORMAT(@d, 'MMMM dd, yyyy') AS "MMMM dd, yyyy",
        FORMAT(@d, 'MMM yyyy') AS "MMM yyyy";

Result:

MM/dd/yyyy  dd-MM-yyyy  yyyy-MM-dd  MMMM dd, yyyy   MMM yyyy
----------  ----------  ----------  --------------  --------
03/24/2035  24-03-2035  2035-03-24  March 24, 2035  Mar 2035

Each format string consists of one or more format specifiers. For example, MM is a format specifier, as is yyyy. You can see that the way we construct the format string determines exactly how the date is output.

Here’s a quick reference for the most common format specifiers:

Token	Description	Example
`yyyy`	Four-digit year	2026
`yy`	Two-digit year	26
`MM`	Two-digit month	03
`MMMM`	Full month name	March
`MMM`	Abbreviated month name	Mar
`dd`	Two-digit day	24
`d`	Day without leading zero	7 instead of 07

Capitalization matters a lot here. MM is month. mm is minutes. Mix them up and you’ll get unexpected results.

Using the Culture Parameter

The FORMAT() function accepts an optional third argument which lets you format a date according to a specific locale. This is handy when your app serves users in different countries, or when you need output that matches a particular regional standard.

You pass in a culture code as a string. Things like 'en-US' for US English, 'de-DE' for German, or 'fr-FR' for French. SQL Server then formats the date the way that region would expect to see it.

DECLARE @d DATE = '2026-03-24';
SELECT  FORMAT(@d, 'D', 'en-US') AS "US English",
        FORMAT(@d, 'D', 'de-DE') AS "German",
        FORMAT(@d, 'D', 'fr-FR') AS "French";

Result:

US English               German                   French            
-----------------------  -----------------------  ------------------
Tuesday, March 24, 2026  Dienstag, 24. März 2026  mardi 24 mars 2026

The 'D' format here is a standard .NET date format that produces the full long date. The culture parameter is what changes the language and ordering of the output. Without it, SQL Server falls back to the date format of the current session (which defaults to a value based on the language setting of the current session), which may or may not be what you want.

If you’re building reports that only ever go to one audience, you probably don’t need this. But if regional formatting matters, it’s good to know it’s there.

Using `CONVERT()` to Format a Date

The CONVERT() function uses numeric style codes instead of format strings. It’s less intuitive but faster, which is why you’ll still see it all over the place in older SQL code.

When converting/formatting dates, it goes something like this:

CONVERT(VARCHAR, date_value, style_code)

The style code is just a number that maps to a specific date format.

Here are some of the more useful ones:

Style Code	Format	Example Output
101	mm/dd/yyyy (US)	03/24/2026
103	dd/mm/yyyy (UK/European)	24/03/2026
112	yyyymmdd (ISO compact)	20260324
120	yyyy-mm-dd hh:mi:ss (ISO with time)	2026-03-24 14:30:00
106	dd Mon yyyy	24 Mar 2026
107	Mon dd, yyyy	Mar 24, 2026

So to get a US-style date, you’d write:

DECLARE @d DATE = '2035-03-24';
SELECT CONVERT(VARCHAR, @d, 101);

Result:

03/24/2035

`FORMAT()` vs `CONVERT()` – Which Should You Use?

Both functions get the job done, and you’ll probably use both over time, but in different situations. Here’s a simple way to think about it:

Use FORMAT() when you need a custom output and readability matters. The format string syntax is intuitive, easy to remember, and simple to adjust when requirements change. It also gives you much more flexibility. You can produce any date pattern you want using .NET format strings, including things like full day names or mixed custom patterns that CONVERT() simply can’t do. It’s often the better choice for one-off queries, reports, and anything where you’re writing SQL that other people will need to read and maintain.
Use CONVERT() when performance is a concern and a standard format is sufficient. FORMAT() is slower because it relies on .NET formatting under the hood, and that overhead adds up on large datasets. If you’re querying millions of rows and one of CONVERT()‘s ~30 built-in style codes covers your needs, it’s the smarter pick. It’s also worth knowing if you’re working in a codebase that already uses it heavily.

For everyday querying and reporting on reasonably sized data, FORMAT() is generally the easier option.

There’s also a third option worth mentioning: don’t format dates in SQL Server at all. Many developers prefer to return raw date values from the database and handle formatting in the application layer, where you have more control over localization, display logic, and presentation concerns. If your app already has a date formatting layer, adding it in SQL too just creates redundancy.

Formatting Dates from a Table Column

Everything above uses a declared variable as an example, but in real life you’re almost always pulling from a table. It works exactly the same way. Just swap in your column name:

SELECT 
    order_id,
    FORMAT(order_date, 'MM/dd/yyyy') AS formatted_date
FROM orders

A Common Gotcha: Formatted Dates are Actually Text

Worth flagging because it catches beginners off guard. When you use FORMAT() or CONVERT() to format a date, the result is a string, not a date. That means you can’t do date math on it or use it in a WHERE clause the same way you would a real date column.

For this reason, you should generally format your dates in the SELECT part of the query, after you’ve done any filtering or calculations. Don’t filter or sort on the formatted version. Of course as mentioned, you also have the option of not formatting dates at all in SQL Server, and letting the application do it (if applicable).

Getting Just the Year, Month, or Day

If you only need part of a date, SQL Server has built-in functions for that too:

DECLARE @d DATE = '2035-03-24';
SELECT 
    YEAR(@d),
    MONTH(@d),
    DAY(@d);

Result:

Year  Month  Day
----  -----  ---
2035  3      24

These are simpler than FORMAT() and useful when you just need a number to work with rather than a formatted string.

Quick Reference

Task	Function
Custom format (flexible)	`FORMAT(date, 'format_string')`
Standard format (fast)	`CONVERT(VARCHAR, date, style_code)`
Just the year/month/day	`YEAR()`, `MONTH()`, `DAY()`
Current date/time	`GETDATE()`

Once you’ve used these a few times, the syntax will become second nature.

The Two Functions You Need to Know

Using FORMAT() to Format a Date

Using the Culture Parameter

Using CONVERT() to Format a Date

FORMAT() vs CONVERT() – Which Should You Use?

Formatting Dates from a Table Column

A Common Gotcha: Formatted Dates are Actually Text

Getting Just the Year, Month, or Day

Quick Reference

Using `FORMAT()` to Format a Date

Using `CONVERT()` to Format a Date

`FORMAT()` vs `CONVERT()` – Which Should You Use?