SQL Server DATE_FORMAT() Equivalent: How to Format Dates in SQL Server

If you’ve come from MySQL and you’re trying to use DATE_FORMAT() in SQL Server, you’ve already hit the problem: it doesn’t exist. SQL Server has no DATE_FORMAT() function. The good news is it has two functions that do the same job, and in some cases do it better.

Why DATE_FORMAT() Doesn’t Work in SQL Server

DATE_FORMAT() is a MySQL function. It’s not part of standard SQL, so it only works in certain other DBMSs like MySQL and MariaDB. When you run it in SQL Server, you’ll get an error like this:

Msg 195, Level 15, State 10, Server c08c931f9ad9, Line 2
'date_format' is not a recognized built-in function name.

SQL Server has its own functions for formatting dates. They work differently from DATE_FORMAT(), but once you understand the pattern they’re straightforward to use.

The Two SQL Server Alternatives

SQL Server gives you two main options for date formatting: CONVERT() and FORMAT(). They both get the job done, but they work differently and suit different situations.

Option 1: `CONVERT()`

CONVERT() uses numeric style codes to control the output format. Instead of writing a format string like '%d/%m/%Y', you pass in a number that maps to a pre-defined format.

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

Example output:

24/06/2026

The 103 is the style code for DD/MM/YYYY. There are codes for most common date formats, covering US, UK, ISO, and several others.

Option 2: `FORMAT()`

FORMAT() is closer to how DATE_FORMAT() works in MySQL. You pass in a format string using .NET format specifiers, which gives you more flexibility than CONVERT().

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');

Example result:

24/06/2026

The format string syntax is different from MySQL’s, but the concept is the same in that you describe the output pattern and the function applies it.

MySQL `DATE_FORMAT()` vs SQL Server Equivalents

Here’s how common DATE_FORMAT() patterns map to their SQL Server equivalents:

MySQL DATE_FORMAT()	SQL Server CONVERT()	SQL Server FORMAT()	Output
DATE_FORMAT(date, ‘%d/%m/%Y’)	CONVERT(VARCHAR, date, 103)	FORMAT(date, ‘dd/MM/yyyy’)	24/06/2026
DATE_FORMAT(date, ‘%m/%d/%Y’)	CONVERT(VARCHAR, date, 101)	FORMAT(date, ‘MM/dd/yyyy’)	06/24/2026
DATE_FORMAT(date, ‘%Y-%m-%d’)	CONVERT(VARCHAR, date, 23)	FORMAT(date, ‘yyyy-MM-dd’)	2026-06-24
DATE_FORMAT(date, ‘%d %b %Y’)	CONVERT(VARCHAR, date, 106)	FORMAT(date, ‘dd MMM yyyy’)	24 Jun 2026
DATE_FORMAT(date, ‘%Y%m%d’)	CONVERT(VARCHAR, date, 112)	FORMAT(date, ‘yyyyMMdd’)	20260624
DATE_FORMAT(date, ‘%Y-%m-%d %H:%i:%s’)	CONVERT(VARCHAR, date, 120)	FORMAT(date, ‘yyyy-MM-dd HH:mm:ss’)	2026-06-24 09:15:00
DATE_FORMAT(date, ‘%b %d, %Y’)	CONVERT(VARCHAR, date, 107)	FORMAT(date, ‘MMM dd, yyyy’)	Jun 24, 2026

`FORMAT()` String Reference

If you’re used to MySQL’s %d, %m, %Y style specifiers, the .NET format strings that FORMAT() uses will feel slightly different. Here’s a quick translation:

MySQL Specifier	SQL Server Specifier	Meaning	Example
%Y	yyyy	Four-digit year	2026
%y	yy	Two-digit year	26
%m	MM	Month as number (zero-padded)	06
%c	M	Month as number (no padding)	6
%b	MMM	Abbreviated month name	Jun
%M	MMMM	Full month name	June
%d	dd	Day of month (zero-padded)	04
%e	d	Day of month (no padding)	4
%H	HH	Hour, 24-hour clock	09
%h	hh	Hour, 12-hour clock	09
%i	mm	Minutes	15
%s	ss	Seconds	00
%p	tt	AM/PM	AM

One thing to watch out for is that SQL Server’s format specifiers are case-sensitive. MM means month, mm means minutes. Get them mixed up and your output will be wrong in a way that’s easy to miss.

Which One Should You Use?

If you’re migrating from MySQL and want something that feels familiar, FORMAT() is the closer match. The format string approach is intuitive and flexible, especially for unusual date patterns that CONVERT() doesn’t have a style code for.

That said, FORMAT() is noticeably slower than CONVERT() on large datasets. It runs through the .NET runtime rather than SQL Server’s native engine, which adds overhead. For queries running against millions of rows, that difference matters.

A simple way to think about it:

Use FORMAT() when you need a format that CONVERT() can’t produce, or when you’re working with small datasets and readability matters more than performance
Use CONVERT() for everything else. It’s faster, it covers the most common formats, and it’s been part of SQL Server for much longer

Examples Side by Side

Here’s a query that produces the same output using both functions so you can see them in action together:

SELECT
  -- Using CONVERT()
  CONVERT(VARCHAR, GETDATE(), 103)  AS convert_uk,
  CONVERT(VARCHAR, GETDATE(), 107)  AS convert_readable,
  CONVERT(VARCHAR, GETDATE(), 112)  AS convert_compact,
  CONVERT(VARCHAR, GETDATE(), 120)  AS convert_datetime,

  -- Using FORMAT()
  FORMAT(GETDATE(), 'dd/MM/yyyy')            AS format_uk,
  FORMAT(GETDATE(), 'MMM dd, yyyy')          AS format_readable,
  FORMAT(GETDATE(), 'yyyyMMdd')              AS format_compact,
  FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')   AS format_datetime

Both approaches return the same results. The choice between them comes down to your format requirements and how much data you’re working with.

A Note on `CAST()`

You might also come across CAST() when looking at date formatting in SQL Server. It’s worth knowing about, but it’s not really a formatting function. CAST() converts a value from one data type to another, but it gives you no control over how the output looks. CAST(GETDATE() AS VARCHAR) will return a date string, but in whatever default format SQL Server decides to use.

If you need a specific format, stick with CONVERT() or FORMAT().

Why DATE_FORMAT() Doesn’t Work in SQL Server

The Two SQL Server Alternatives

Option 1: CONVERT()

Option 2: FORMAT()

MySQL DATE_FORMAT() vs SQL Server Equivalents

FORMAT() String Reference