Convert MMDDYYYY to DATE in SQL Server

Sometimes we get dates in a format that SQL Server has trouble with when we try to convert them to an actual DATE value. One example would be dates in MMDDYYYY format. While it might be easy to assume that SQL Server would be able to handle this easily, when we stop to think about it, this format is fraught with danger.

The MMDDYYYY format is ambiguous. While we might know that the first two digits are for the month, SQL Server doesn’t know this. Some countries/regions use the first two digits for the day (like DDMMYYYY). So if we get a date like, 01032025, how would SQL Server know whether it’s the first day of the third month, or the third day of the first month?

This ambiguity is why we need to do more than a simple data conversion. Below are a few approaches to converting dates provided in MMDDYYYY format to a valid DATE value.

Option 1: The DATEFROMPARTS() and SUBSTRING() Combo

One way to convert MMDDYYYY to a DATE value is to use the DATEFROMPARTS() function. We can use this in combination with the SUBSTRING() function to piece together each date part from the original value:

SELECT DATEFROMPARTS(
  SUBSTRING('08022025', 5, 4), -- Year (2025)
  SUBSTRING('08022025', 1, 2), -- Month (08)
  SUBSTRING('08022025', 3, 2)  -- Day (02)
);

Result:

FormattedDate   
----------------
      2025-08-02

Here it is using variables for the date and date string:

DECLARE @DateString VARCHAR(8) = '08022025';
DECLARE @FormattedDate DATE;

SET @FormattedDate = DATEFROMPARTS(
    SUBSTRING(@DateString, 5, 4), -- Year (2025)
    SUBSTRING(@DateString, 1, 2), -- Month (08)
    SUBSTRING(@DateString, 3, 2)  -- Day (02)
);

SELECT @FormattedDate AS FormattedDate;

Result:

FormattedDate   
----------------
      2025-08-02

Option 2: The CONVERT() and STUFF() Combo

Another way to do it is by combining STUFF() and CONVERT():

SELECT CONVERT(DATE, STUFF(STUFF('08022025', 3, 0, '/'), 6, 0, '/'), 101) AS FormattedDate;

Result:

FormattedDate   
----------------
      2025-08-02

Here’s a breakdown of each stage:

• STUFF('08022025', 3, 0, '/'): This inserts a / at the 3rd position (after MM). The string becomes '08/022025'.
• STUFF(..., 6, 0, '/'): This inserts another / at the 6th position (after DD). The string becomes '08/02/2025'.
• CONVERT(DATE, ..., 101): The final string is now in a format that the CONVERT function with style 101 can reliably parse.

We can also swap out CONVERT() for TRY_CONVERT():

SELECT TRY_CONVERT(DATE, STUFF(STUFF('08022025', 3, 0, '/'), 6, 0, '/'), 101) AS FormattedDate;

Result:

FormattedDate   
----------------
      2025-08-02

The TRY_CONVERT() function returns NULL in the event that the conversion fails. The CONVERT() function on the other hand would return an error.

Option 3: The CONVERT() and SUBSTRING() Combo

We can also use SUBSTRING() with CONVERT():

DECLARE @mmddyyyy VARCHAR(8) = '08022025';

SELECT CONVERT(DATE,
  SUBSTRING(@mmddyyyy, 5, 4) + '-' +
  SUBSTRING(@mmddyyyy, 1, 2) + '-' +
  SUBSTRING(@mmddyyyy, 3, 2)) AS FormattedDate;

Result:

FormattedDate   
----------------
      2025-08-02

This is a classic string manipulation approach where you extract the month, day, and year parts and then reassemble them into a format that SQL Server can easily understand.

Here’s a breakdown:

• SUBSTRING(@mmddyyyy, 5, 4): Extracts the YYYY part (starting at position 5, with a length of 4).
• SUBSTRING(@mmddyyyy, 1, 2): Extracts the MM part (starting at position 1, with a length of 2).
• SUBSTRING(@mmddyyyy, 3, 2): Extracts the DD part (starting at position 3, with a length of 2).

The parts are concatenated with hyphens (-) to form the standard YYYY-MM-DD format, which is easily convertible to a DATE type.

As with the previous example, we can switch out CONVERT() for TRY_CONVERT() if needed.

Further Date Formatting

Once the value is a valid DATE type, you can use it as a date (for example, to perform date calculations). But you can also perform further formatting on it in order to display it in a way that suits the specific requirements of a given scenario. For example, we can use the FORMAT() function to present the date as a formatted string:

DECLARE @mmddyyyy VARCHAR(8) = '08022025';

SELECT FORMAT(
        CONVERT(DATE,
            SUBSTRING(@mmddyyyy, 5, 4) + '-' +
            SUBSTRING(@mmddyyyy, 1, 2) + '-' +
            SUBSTRING(@mmddyyyy, 3, 2)
            ),
            'MMMM d", "yyyy'
        ) AS FormattedDate;

Result:

FormattedDate
--------------
August 2, 2025  

Best Practices

• TRY_CONVERT(): Whenever possible, use TRY_CONVERT() instead of CONVERT() to avoid errors on malformed data.
• Performance: For large datasets, CONVERT() and TRY_CONVERT() are generally the most performant. String manipulation functions like STUFF() and SUBSTRING() can add overhead.
• Readability: Choose the method that is most readable and maintainable for your team. Most people find CONVERT() and SUBSTRING() clear and easy to understand.
• SQL Server Version: Be mindful of your SQL Server version. FORMAT() and TRY_CONVERT() were introduced in SQL Server 2012.