Formatting DATE as MMDDYYYY in SQL Server

In SQL Server, we have several options when it comes to formatting a DATE or DATETIME value as MMDDYYYY. The two most common functions for formatting dates like this are CONVERT() and FORMAT().

FORMAT() Function

The FORMAT() function allows us to format dates using a format string. Introduced in SQL Server 2012 it uses .NET Framework format strings.

The syntax goes like this:

FORMAT(value, format_string, culture)

The optional culture argument is a handy feature that allows us to specify a locale for the date format. For example, en-US for US English. However, for the purpose of this article, we’re explicitly formatting the date as MMDDYYYY and so there’s no need to use the culture argument.

Using FORMAT() for MMDDYYYY

The FORMAT() function makes this task very simple and direct. You can use a custom format string like 'MMddyyyy':

SELECT FORMAT(GETDATE(), 'MMddyyyy');

Output:

08012025

This directly formats the current date using the specified custom format string. MM represents the month with a leading zero, dd represents the day with a leading zero, and yyyy represents the four-digit year. This particular format string is considered a custom format string, due to the fact that we explicitly specify where each component goes in the output. The FORMAT() function also accepts standard date/time format strings, but none of those are suitable for our specific situation.

The FORMAT() function is more flexible than the CONVERT() function as far as supported formats goes, and the format strings are usually more readable than the style codes that CONVERT() relies on.

CONVERT() Function

The CONVERT() function is a standard SQL Server function for type conversion. It can be used to convert a date to a string and format it using specific style codes.

The syntax goes like this:

CONVERT(data_type, expression, style_code)

However, there isn’t a direct MMDDYYYY style code. So we’ll need to use a style that’s close, and then manipulate the string to produce the result we want.

Option 1: Using Style 101 and REPLACE()

A straightforward approach with CONVERT() is to use CONVERT(VARCHAR, GETDATE(), 101) which returns MM/DD/YYYY. Then, we can use REPLACE() to remove the slashes:

SELECT 
    GETDATE() AS raw_date,
    CONVERT(VARCHAR(10), GETDATE(), 101) AS 'MM/DD/YYYY',
    REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS 'MMDDYYYY';

Output:

raw_date                  MM/DD/YYYY  MMDDYYYY
------------------------  ----------  --------
2025-08-01T04:36:35.180Z  08/01/2025  08012025

So we can see that:

• CONVERT(VARCHAR(10), GETDATE(), 101) converts the current date to a string in the format MM/DD/YYYY.
• REPLACE(..., '/', '') replaces all occurrences of ‘/‘ with an empty string.

Option 2: Using Style 112 and SUBSTRING()

Another approach is to use a style that provides YYYYMMDD (style 112) and then rearrange the parts. We can use string manipulation functions like SUBSTRING() to rearrange the parts. This is less intuitive than the REPLACE() method (and even more convoluted), but at least it’s out there as an option:

DECLARE @dateString VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112); -- '20250801'

SELECT SUBSTRING(@dateString, 5, 2) + -- Get MM part
       SUBSTRING(@dateString, 7, 2) + -- Get DD part
       SUBSTRING(@dateString, 1, 4);   -- Get YYYY part

Output:

08012025

Same result, even if it did require a lot more code.

Comparison of CONVERT() and FORMAT()

Here’s a quick comparison of the two methods we used here today:

Feature	CONVERT()	FORMAT()
Availability	All versions of SQL Server.	SQL Server 2012 and later. Not
Syntax	Uses numeric style codes.	Uses .NET Framework format strings (more intuitive).
Flexibility	Limited to predefined styles. Often requires string manipulation (REPLACE, SUBSTRING).	Highly flexible. Supports custom format strings and culture-specific formatting.
Performance	Generally faster for simple conversions, as it is a native SQL Server function.	Can be slightly slower due to its reliance on the CLR (.NET Framework), but the difference is often negligible for most applications.
Readability	Less readable, as you must remember the style codes.	More readable and self-documenting due to the use of descriptive format strings.

So in a nutshell, for formatting dates in a formatted string like MMDDYYYY, FORMAT() is probably the obvious choice due to its clarity and flexibility (provided you are using SQL Server 2012 or later).